Click here to Skip to main content
13,902,840 members
Click here to Skip to main content
Add your own
alternative version


10 bookmarked
Posted 16 Mar 2012
Licenced CPOL

Copy/Synchronize Table Data between databases

, 16 Mar 2012
Rate this:
Please Sign up or sign in to vote.
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 Developer, Consultant, Architect, Blogger, Speaker and a Microsoft Employee. He is passionate about programming.
He is extensively involved in Asp.Net Core, MVC/Web API, 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:

Disclaimer: Any posts, article, content present in this website is my personal opinion based on best of my knowledge and experience. None of the content provided by me should be considered as official communication/opinion or my Employer.

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.190306.1 | Last Updated 16 Mar 2012
Article Copyright 2012 by Anurag Gandhi
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid