Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Identity Insert to keep SQL Server table keys in sync


By:   |   Updated: 2006-09-26   |   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.

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: 2006-09-26


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Thank you so much Greg.


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

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

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

 Thanks, that solved my problem


Learn more about SQL Server tools