SQL Server Identity Insert to Keep Tables Synchronized

By: Greg Robidoux   |   Updated: 2020-07-09   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | > 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.

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

About the author

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