Click here to Skip to main content
11,639,765 members (60,054 online)
Click here to Skip to main content

Copy/Synchronize Table Data between databases

, 16 Mar 2012 CPOL 15.1K 9
Rate this:
Please Sign up or sign in to vote.
A Query to copy an individual 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.

License

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

Share

About the Author

Anurag Gandhi
Architect
India India
Anurag Gandhi currently works in web application design/development and has done so for many years now as he is passionate about programming.
He is extensively involved in Asp.Net and Asp.Net MVC web application architecture, AngularJs, design and development. His languages of choice are C#, Asp.Net, MVC, Asp, C, C++. But he is familiar with many other coding languages. He works with MS Sql Server as the database architecture of choice.
He is active in programming communities and loves to share his knowledge with other developers whenever he gets the opportunity.
He is also a passionate chess player.

He can be contacted at: soft.gandhi@gmail.com

You may also be interested in...

Comments and Discussions

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