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…