How to import data in SQL Compact Edition?

Question

I don't seem to find a tool for it, nor an odbc driver.

Thanks

UPDATE : I'm aware of the sql scripting possibilities. But than again : how to script a sql 2k table? (not just ddl, but data also?)

Of course you can write this all by yourself, but importing data into CE cannot be such a hassle, or can it ?

UPDATE2 : I don't seem to be able to choose the right dialect for inserting

Answer 1

You can use SQL Server Management Studio Express which also allows the connection to SQL Compact databases. You'll then be able to execute any *.sql file easily that have data exported from elsewhere.

Edit (response to update): I don't know if you can connect to a SQL Server 2000 with SSMS 2008, but if you can, right click on your database in SSMS, choose Tasks > Generate Scripts, and in the options on the second page, scroll down and change "Script Data" to True.

Answer 2

See my answer to this SO question here:

http://stackoverflow.com/questions/1515969/exporting-data-in-sql-server-as-insert-into/1515975

There are quite a few SSMS plugins available to handle both structure and data in a table and will output INSERT statements to fill the table.

And of course, there's the whole "ADO.NET Sync Services" area which would allow you to programmatically fill your SQL CE databases on demand.

Marc

Answer 3

I have a list of 3rd party tools for SQL Compact, some of which does data migration. This list is located here: http://erikej.blogspot.com/2009/04/sql-compact-3rd-party-tools.html