Copy/Synchronize Table Data between databases

Introduction

Many times it is required to copy table data between databases. There are a lot of ways to do so. But when you wish to synchronize the data too often, you may need few lines of query to do the job. This tip demonstrates the way to do this.  

Background

Many times it is required to copy data (of 1 or 2 tables only) from your live server to local server or between different databases. For such cases, you can use simple query to this.  

Using the code

Using the code is simple. Comment at each line of query is written to simplify things. 

To use the code you must need to create a linked server you are using. If linked server is not created, go to http://msdn.microsoft.com/en-us/library/aa560998(v=bts.10).aspx. 

-- Uncheck all constraint.
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
-- Delete the existing table
-- We are not using truncate intentionally as truncate will check key consraint and may not work.
delete from TableName
-- Resetting the identity column
DBCC CHECKIDENT (TableName, RESEED, 0)

-- Inserting Data from remote/other linked server to my database. The vice versa can also be done.
set Identity_Insert TableName on
insert into TableName([Column1], [Column2], [Column3], ...)
select * from [ServerName].[DatabaseName].dbo.TableName
set Identity_Insert TableName off
-- Enabling check constraint.
exec sp_msforeachtable @command1= 'print ''?''', @command2='ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

Now you are done. Verify your data at source and destination table. 

Please note that to do so, the schema of source table and destination table must be same.

Points of Interest 

This tip will also guide you on how to:

• UnCheck all constraint. 
• Reset the identity column value. 
• Use Identity Insert. 
• Copy data from one table to another table. 

History

Version1: March 16, 2012.