SQL Server Identity Insert to Keep Tables Synchronized

By:   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | > Identities


One thing that DBAs are often faced with is moving data from one database to another database to populate lookup tables or some other key table in your database. This may be to keep a test or development environment in synch or maybe there is a need to populate like databases on other servers with the same data values.

Another thing that is often common with SQL Server is the use of identity values or auto incrementing of a key value for new records as they get inserted.  Using identity values is a simple way to make sure you have a unique primary key for new records, but there is no simple way to control what identity value will be given for a certain row in a table. 

When you combine these two items together, having an identity column as a primary key and having the need to push like data to other databases this is where the problems begin.  If your tables on these different databases are setup exactly the same way where both tables have an identity value there is no way to control what identity value one table will get vs the other table and therefore you can have issues where the data is not in synch.


To handle this situation where you need to make sure the data on your two databases stays in synch along with having an identity value for the key, SQL Server has a command SET IDENTITY_INSERT that allows you to turn on and off the auto generation of the identity value as needed.  The command gets turned on prior to the inserting of data and gets turned off after the data has been inserted.

Here is a simple command that allows you to insert into a table that has an identity value field, but allows you to specify what the value will be instead of having SQL Server pick the next sequential value.

This first query is our regular insert into our primary database.  At this point we don't need to specify the ClientID, because this value is auto generated from our identity value on the ClientID column.

INSERT INTO dbo.CLIENT (ClientName) VALUES ('Edgewood Solutions') 
INSERT INTO dbo.CLIENT (ClientName) VALUES ('Microsoft')

After we insert the data we have two new records in our Client table.  The ClientID value was auto generated.

ClientID ClientName
782 Edgewood Solutions
783 Microsoft

To move this data to our other databases that have the same table and need to retain the same identity values we issue the command below.  First we turn on the identity insert, insert the two records this time including the ClientID value and then we turn off the identify insert to make sure that if any future records get added the clientID column is auto generated.

INSERT INTO dbo.CLIENT (ClientID, ClientName) VALUES (782, 'Edgewood Solutions')
INSERT INTO dbo.CLIENT (ClientID, ClientName) VALUES (783, 'Microsoft')

By using the IDENTITY_INSERT command we are able to make sure that the records that get inserted on our other databases retain the same ClientID value, this way there are no data integrity issues from one database to another.

Next Steps
  • Next time you need to populate like tables on different databases that have identity values look at using this command to make the maintenance of these tables easier

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, May 16, 2013 - 12:52:19 PM - Lynn Nguyen Back To Top (23993)

Thank you so much Greg.

Thursday, June 10, 2010 - 12:26:28 PM - admin Back To Top (5680)

If there are more columns in the second table then you need to make sure the columns in the INSERT and SELECT portion match up.

If the key already exists in the destination table then you will get a viloation error and the entire command will roll back.  You can change your SELECT statement to only include rows that do not already exist by using a NOT IN or a NOT EXISTS clause in the query.

Thursday, June 10, 2010 - 12:03:36 PM - DavidScott Back To Top (5679)

What happens when the second table is larger, and the two keys have perhaps been already allocated ?


Wednesday, May 26, 2010 - 11:02:57 AM - itbreach Back To Top (5545)

 Thanks, that solved my problem

get free sql tips
agree to terms