Using Identity Insert to keep SQL Server table keys in sync


By:   |   Updated: 2020-07-09   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | More > Identities

Problem

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.

Solution

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.

SET IDENTITY_INSERT dbo.Client ON 
INSERT INTO dbo.CLIENT (ClientID, ClientName) VALUES (782, 'Edgewood Solutions')
INSERT INTO dbo.CLIENT (ClientID, ClientName) VALUES (783, 'Microsoft')
SET IDENTITY_INSERT dbo.Client OFF

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


Last Updated: 2020-07-09


get scripts

next tip button



About the author





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



download


Recommended Reading

Managing and maintaining SQL Server identity values

Properly Capturing Identity Values in a SQL Server Database

Auto generated SQL Server keys with the uniqueidentifier or IDENTITY

Populate a SQL Server column with a sequential number not using an identity

SQL Server 2012 Sequence Numbers





get free sql tips
agree to terms


Learn more about SQL Server tools