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:
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?
No, Sql Server CE does not support IF (or BEGIN and END for that matter)
Would allowing the batch to fail (ie. a "continue on errors" feature) help you?
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.
> Would allowing the batch to fail help you?
Yes, that would be nice to have this option as well.
I have logged the two request, thanks for the proposals
I think it already returns 1 on errors??
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?
Use SqlCeCmd40.exe for 4.0 databases
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;"
Rene: Use the full path
Hi Erik,
Instead of using the full path, I am using %AppData%\filename.sdf which works good.
Rene
Post a Comment