Problem
One thing that DBAs are often faced with is moving data from one database to another database. This could be to populate lookup tables or other key tables in your database. This may be to keep a test or development environment in synch or to populate like databases on other servers with the same data values.
When a table has an identity column and you want to keep the same value, you can’t do a straight insert without the possibility of having different identity values on each system. So, how do you get around this?
Solution
To handle this situation where you need to make sure the data on your two databases stays in synch along with the identity value, SQL Server has a command SET IDENTITY_INSERT. This allows you to turn on and off the auto generation of the identity value as needed and specify the value you want to use.
The command gets turned on prior to inserting data and gets turned off after the data has been inserted.
Sample Table
Let’s create a table and insert some data to see how this works.
CREATE TABLE dbo.Client (ClientId int identity(1,1), ClientName varchar(100));The query below creates two new records. 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 along with ClientID values.
SELECT * FROM dbo.Client;| ClientID | ClientName |
|---|---|
| 1 | Edgewood Solutions |
| 2 | Microsoft |
Insert Same Data and Values
To move this data to other databases that have the same table and retain the same identity values we need to use SET IDENTITY_INSERT.
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.
Below is the command. Note, that we need to specify all of the columns in the table (ClientID and ClientName) and provide values for each of these columns.
SET IDENTITY_INSERT dbo.Client ON
INSERT INTO dbo.CLIENT (ClientID, ClientName) VALUES (1, 'Edgewood Solutions')
INSERT INTO dbo.CLIENT (ClientID, ClientName) VALUES (2, 'Microsoft')
SET IDENTITY_INSERT dbo.Client OFFBy using the IDENTITY_INSERT command we are able to make sure that the records that get inserted on other databases retain the same ClientID value, this way there are no data integrity issues from one database to another.
Things to Note
If the table you are inserting into does not have a primary key or unique key on the identity column and you insert the same value multiple times, it will create duplicate values.
If the table does have a primary key or unique key on the identity column and you try to insert a value that already exists you will get an error.
Next Steps
- Next time you need to populate like tables on different databases that have identity values look at using SET IDENTITY_INSERT to make the maintenance of these tables easier.
- Also, check out this related article: SQL Server Identity Values Managing and Updating
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.