How to export Tables from SQL Server Compact Edition to SQL Server Express (.sdf to .mdf)
In this article I will show an easy workaround to seamlessly export Tables from SQL Server Compact Edition(.sdf) to SQL Server Express(.mdf) in Visual Studio 2008 using CEQuery and Visual Studio's query editor.
As I explained in the summary I will show an easy way to convert .sdf database file to .mdf database file using query editor in Visual Studio 2008.1. Generate Script
There is a free open source software called CEQuery which will generate the script for you. You can download it from this link http://cequery.codeplex.com/ and install it.
i. Open the SQLCE database you want to export in CEQuery. Click on "Script Generator" it will bring the ScriptGenerator dialog.
ii. Select whether you want to export only schema or both schema and data.
iii. Select any database option doesn't matter.
iv. Select the tables to be exported.
v. Select the save location and click OK. Your script is now ready.2. Run the Script
i. Open the script you had previously generated in a notepad or any word editor. Copy the contents.
ii. Create and add a new database(.mdf) to your project. If you don't know how to go about it then follow this link http://www.dotnetspider.com/resources/44548-How-make-updates-stick-database.aspx
iii. Go to View -> "Server Explorer". Right click the Tables folder and select "New Query". Paste the contents in the query editor.
iv. Right click and select "Execute SQL". You will get a warning saying "The CREATE TABLE SQL construct or statement is not supported." Just ignore it and click on Continue. Now you will get a message that Query was executed successfully. Click OK.
Don't panic if you don't see any Tables in the database just refresh the database. So isn't it easy?