SQL Compact Table Copy

How do you make a copy of a SQL Compact table? Version 3.5 does not support the SELECT INTO command that allows you to easily do just that. Instead, it provides the INSERT INTO SELECT FROM command that allows you to copy existing table data into another existing table. So we are only missing one part: how to create a replica of the existing table structure.

If you want to stick to managed code only, you can read all the database structure information from the INFORMATION_SCHEMA views. To get information ot of these views, you just have to issue a SELECT command. For instance:

SELECT * FROM INFORMATION_SCHEMA.TABLES

returns all the existing tables in the database. In our case, we need to get information from an existing table and build a CREATE TABLE command out of it in order to create a replica of the original table. For this purpose, you can issue the following SQL command:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName' ORDER BY ORDINAL_POSITION

With a little work you can read from this data and build a CREATE TABLE command. If you look at the returned data, you will see that you have all the information you need. In fact, you just need to set up a StringBuilder in your code, and start adding the columns and their types. You can fin this in the sample code (see below), although the code still does not support the numeric data type.

After creating the table, you can now copy the data, making sure that you execute a SET IDENTITY_INSERT command on the target table if it has an identity column.

The sample code has a very simple UI that allows you to specify a connection string, the original and target table names and a button to perform the copy.

Sample code: CopyTableSample.zip