Migrate data from one SQL table to another database table

Sometimes you may have to migrate data from one SQL database to another SQL database on another environment, e.g. Live to test and dev environment and vice versa.

You may argue the easiest solution is a migration of the backup of database. Unfortunately it does not work well in environments where data in Live is classified as sensitive and are not allowed to be copied across into a dev / test machine where the security is not as strict as a live machine. There are some data from live which are not classified as sensitive or restricted and might have to be migrated to Test and DEV for testing or development purposes, for example list of values.

A copy of the data can be exported as a csv or dat file from the SQL Table using “select * from table_value” statement from source database.

The data can be bulk imported into a temp table and MERGE statement can be used to insert missing records and update records. The sample script which you can use is below.

The script can be downloaded from TechNet.

	BEGIN TRAN
	if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tmp_table_value') and xtype='U')
	drop table #tmp_table_value
	CREATE TABLE #tmp_table_value
	([table_id] [int] NOT NULL,
	[value_date] [datetime] NOT NULL,
	[raw_value] [decimal](12, 6) NULL
	)
	BULK INSERT #tmp_table_value FROM 'C:\data.csv' WITH ( FIRSTROW = 2 , FIELDTERMINATOR = ',' , rowterminator = '\n',KEEPNULLS)
	–table_value is the target table
	— the temp table tmp_table_value is the source table
	MERGE table_value AS T
	USING #tmp_table_value AS S
	ON (T.table_id = S. table_id and T.value_date = S.value_date)
	WHEN NOT MATCHED
	THEN INSERT(table_id, value_date, raw_value )
	VALUES
	( S.table_id, S.value_date, S.raw_value)
	WHEN MATCHED AND (T.raw_value != S.raw_value )
	THEN UPDATE SET T.raw_value = S.raw_value ;
	select * from table_value order by value_date desc
	if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tmp_table_value') and xtype='U')
	drop table #tmp_table_value
	ROLLBACK TRAN;
	–COMMIT TRAN;

view raw

Migrate SQL Table data to another database

hosted with ❤ by GitHub