Tuesday, May 12, 2009

SqlCeCmd tutorial part two – Creating database objects and adding data

This is the second part of a 3 part series with some examples of sqlcecmd usage. The first part is here. This second part deals with creation of database objects and adding data.

To create a table

sqlcecmd -d "Data Source=C:\test2.sdf" -q "CREATE TABLE NewTable (Id int NOT NULL, Info nvarchar(50) NULL) "

To create other objects

To create other objects (foreign keys, indexes) use the appropriate CREATE or ALTER statement as documented in SQL Compact BOL.

To rename a table

sqlcecmd -d "Data Source=C:\test2.sdf"
-q "sp_rename 'NewTable', 'New table' "

So in other words, any valid SQL Compact SQL statement (including UPDATE and DELETE) can be executed form the command line with the “q” option.

To insert data in a table

sqlcecmd -d "Data Source=C:\test2.sdf"
-q "INSERT INTO [New Table] (Id, Info) VALUES (1, N’Test’)"

To run multiple statements in a single “batch”

To run multiple staements in a batch, use the “i” option to specify an input file. The input file should be a text file with the desired SQL statements, each separated by the word GO on a line of it own:

INSERT INTO [New Table] (Id, Info) VALUES (2, N'Test2')
GO
INSERT INTO [New Table] (Id, Info) VALUES (3, N'Test3')
GO
INSERT INTO [New Table] (Id, Info) VALUES (4, N'Test4')
GO

(Contents of c:\input.txt)

Once the file has been created, for example with help from the Database Publishing Wizard (in VS 2008) or ExportSqlCe, you can run the commands in the file like so:

sqlcecmd -d "Data Source=C:\test2.sdf" -i c:\input.txt

Next installment: Querying data…

12 comments:

rodpedja said...

Hello,
when running multiple statements in a single “batch” is it possible to do this?:
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Table1'))
BEGIN
ALTER TABLE [Table1] DROP CONSTRAINT [FK_Table1] END
GO

It's not possible to use the 'if' instruction in Sql Server CE right?

ErikEJ said...

No, Sql Server CE does not support IF (or BEGIN and END for that matter)

ErikEJ said...

Would allowing the batch to fail (ie. a "continue on errors" feature) help you?

Anonymous said...

Erik,

Could you please make these two things (important for command line developers):

1. Output errors to stderr;
2. Return 0 on success, error code otherwise.

This is especially useful with the -i option, when updating database from external sources.

As for know I use sed to analyze output.

Anonymous said...

> Would allowing the batch to fail help you?

Yes, that would be nice to have this option as well.

ErikEJ said...

I have logged the two request, thanks for the proposals

ErikEJ said...

I think it already returns 1 on errors??

Anonymous said...

How do I get SqlCeCmd to create a SQL CE 4.0 file? I used the Create command but it creates it in a different version.

Also, I tried sqlcecmd -d "Data Source=MyDB.sdf" -v and SqlCeCmd says "Either -q, -i, -e or -z required." I want to find the information on my file but can't seem to get that to work. What is the command to do that?

ErikEJ said...

Use SqlCeCmd40.exe for 4.0 databases

Pet Diet Designer said...

How do you access an sdf database stored in the Application Data folder? For example, the config file has this partial connection: "data source=|DataDirectory|\pdd03.sdf;password=mypassword;"

ErikEJ said...

Rene: Use the full path

Pet Diet Designer said...

Hi Erik,

Instead of using the full path, I am using %AppData%\filename.sdf which works good.

Rene