How to import data in SQL Compact Edition? [closed]

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:

Exporting data In SQL Server as INSERT INTO

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

Not the best solution heh but...

I tried the Generate Script method above and it did not work out for me because the script generated a "print x records" line for every 400 records. I needed to tranfer 65535 records to a table in SQL Server Compact and the script generated crashed at "print x records". I was not able to figure out how to turn off the print operation in generate script.

After 3 hours of Trial/error and google searches in a windows 7 PC: 1. Use SQL Server management Studio and do a Select statement 2. Right click on the result set and copy 3. Using Visual Studio > open the table 4. Right click paste

In other words I copied and pasted the entire result set to the SQL compact table. Overnight all 65535 records were copied. The down side? 12 hours.