1

I have just one simple table in my MS SQL database (or Microsoft Access). How do I read in this table and write it out as an SQL CE database? I have both Visual Studio and WebMatrix installed so I can use either of these tools if required. Thanks.

Is it possible to copy and paste one table to another? This would be the simplest if it works.

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
user776676
  • 4,265
  • 13
  • 58
  • 77

2 Answers2

5

Try my Visual Studio add-in, allows you to migrate from SQL Server to SQL Server Compact: http://sqlcetoolbox.codeplex.com - command line walkthrough here: http://erikej.blogspot.com/2010/02/how-to-use-exportsqlce-to-migrate-from.html

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • Hi Erik, I downloaded your VS addin and installed it successfully. I created a blank CE 4.0 database and also have an existing 3.5 blank CE database. I couldn't figure out how to migrate an existing SQL db to either of these databases or to a new CE database after bringing up the SQL Server Compact Toolbox. Could you give me some further guidance? I prefer to use your SQL CE Toolbox to do this, not the command line interface. Is this possible? Thanks. – user776676 Jun 20 '11 at 18:45
  • Generate a script from the server database, using "Script SQL Server Database Schema and Data". Open the SQL editor in your new empty SQL CE database, and open the just created script, and Execute it. – ErikEJ Jun 21 '11 at 06:18
1

You can download a free copy SQL Express Management Studio (SSMS).

SSMS has the ability to connect to both full blown SQL, SQL Express, and SQL CE databases.

Within SSMS you can right click on the table and use the wizard to move data back and forth. Or create scripts that will generate the raw table structures that exist in MS SQL, SQL Express and allow you to move them to SQL CE.

The primary wizard you'll probably use in SSMS to move data from an Access table to a SQL, or SQL CE table is called SQL Server Import/Export Wizard.

The learning curve on these wizards is pretty minimal. As your needs grow more advanced and the things you want to do get a little more fancy you'll find that scripting and the tsql language is where you'll want to dedicate some time to learning. But this should get you started with the question at hand.

RThomas
  • 10,702
  • 2
  • 48
  • 61
  • You can also see this SO link for help connecting to the CE database with SSMS. http://stackoverflow.com/questions/577222/how-can-i-manage-sql-ce-databases-in-sql-server-management-studio Note that ealier versions of SSMS Express couldn't connect to CE but 2008 SP1 or later can. – RThomas Jun 20 '11 at 06:05