Click here to Skip to main content
14,636,672 members
Articles » Database » Database » SQL Server
Posted 16 Mar 2012


11 bookmarked

Copy/Synchronize Table Data between databases

Rate this:
5.00 (10 votes)
Please Sign up or sign in to vote.
5.00 (10 votes)
16 Mar 2012CPOL
A Query to copy an individual table data between databases


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.  


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

-- Uncheck all constraint.
-- 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

-- 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. 


Version1: March 16, 2012.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Anurag Gandhi
India India
Anurag Gandhi is a Freelance Developer and Consultant, Architect, Blogger, Speaker, and Ex Microsoft Employee. He is passionate about programming.
He is extensively involved in Asp.Net Core, MVC/Web API, Node/Express, Microsoft Azure/Cloud, web application hosting/architecture, Angular, AngularJs, design, and development. His languages of choice are C#, Node/Express, JavaScript, Asp .NET MVC, Asp, C, C++. He is familiar with many other programming languages as well. He mostly works with MS SQL Server as the preferred database and has worked with Redis, MySQL, Oracle, MS Access, etc. also.
He is active in programming communities and loves to share the knowledge with others whenever he gets the time for it.
He is also a passionate chess player.
Linked in Profile:
He can be contacted at

Comments and Discussions

-- There are no messages in this forum --