Export from SQL Server to SQL Server Compact

Posted to Windows Blog on Nov 04, 2009

It is not possible to use the import / export wizard to easily move data from SQL Server 2005/2008 to SQL Server Compact.

However, I found an example of a stored procedure from this article Generating INSERT statements in SQL Server by Sumit Amar on CodeProject. The procedure can be used to generate insert statements for each row in a table. You can then apply these insert statements to a SQL Compact database using SQL Server Management Studio.

I modified the procedure to add a semicolon at the beginning, so you can run multiple statements from a SQL Management Studio window.

I have used this method to easily create data within a SQL Compact database. I have used it for tables with up to around 100,000 rows, but much more than that, and it would take a long time to cut/paste and run the statements. .

Create InsertGenerator Stored Procedure

CREATE PROCEDURE [dbo].[InsertGenerator] 
	@tableName as varchar(100)
AS
BEGIN

	--Declare a cursor to retrieve column specific information 
	--for the specified table
	DECLARE cursCol CURSOR FAST_FORWARD FOR 
	SELECT column_name,data_type FROM information_schema.columns 
		WHERE table_name = @tableName
	OPEN cursCol
	DECLARE @string nvarchar(3000) --for storing the first half 
								   --of INSERT statement
	DECLARE @stringData nvarchar(3000) --for storing the data 
									   --(VALUES) related statement
	DECLARE @dataType nvarchar(1000) --data types returned 
									 --for respective columns
	SET @string=';INSERT '+@tableName+'('
	SET @stringData=''

	DECLARE @colName nvarchar(50)

	FETCH NEXT FROM cursCol INTO @colName,@dataType

	IF @@fetch_status<>0
		begin
		print 'Table '+@tableName+' not found, processing skipped.'
		close curscol
		deallocate curscol
		return
	END

	WHILE @@FETCH_STATUS=0
	BEGIN
	IF @dataType in ('varchar','char','nchar','nvarchar')
	BEGIN
		SET @stringData=@stringData+'''''''''+
				isnull('+@colName+','''')+'''''',''+'
	END
	ELSE
	if @dataType in ('text','ntext') --if the datatype 
									 --is text or something else 
	BEGIN
		SET @stringData=@stringData+'''''''''+
			  isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
	END
	ELSE
	IF @dataType = 'money' --because money doesn't get converted 
						   --from varchar implicitly
	BEGIN
		SET @stringData=@stringData+'''convert(money,''''''+
			isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
	END
	ELSE 
	IF @dataType='datetime'
	BEGIN
		SET @stringData=@stringData+'''convert(datetime,''''''+
			isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
	END
	ELSE 
	IF @dataType='image' 
	BEGIN
		SET @stringData=@stringData+'''''''''+
		   isnull(cast(convert(varbinary,'+@colName+') 
		   as varchar(6)),''0'')+'''''',''+'
	END
	ELSE --presuming the data type is int,bit,numeric,decimal 
	BEGIN
		SET @stringData=@stringData+'''''''''+
			  isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
	END

	SET @string=@string+@colName+','

	FETCH NEXT FROM cursCol INTO @colName,@dataType
	END


	DECLARE @Query nvarchar(4000) -- provide for the whole query, 
								  -- you may increase the size

	SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') 
		VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' 
		FROM '+@tableName
	exec sp_executesql @query --load and run the built query


	CLOSE cursCol
	DEALLOCATE cursCol


END

Execute InsertGenerator Stored Procedure

After you create the stored procedure above, you can execute it against a table to generate insert statements to the results pane.

Execute Insert Statements on SQL Compact Database

The insert statements can be copied/pasted to another SQL Management Studio window. Hitting the execute button will insert the eight rows shown into the database.