SET IDENTITY_INSERT

Let’s say you have the following table on two servers, one in development and one in production.

CREATE TABLE person (
    personID INT IDENTITY(1,1)
  , firstName VARCHAR(50)
  , lastName VARCHAR(50)
)

You’re about to implement a new query, but you need to test it out in development before you move it to production.  The problem is you need production data to complete your test.  The problem is you can’t simply run

INSERT INTO development.yourDatabase.dbo.person
SELECT *
FROM production.yourDatabase.dbo.person

If you did try to run that you’d get an error like this.

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'person' when IDENTITY_INSERT is set to OFF.

Which should bring you immediately to the question, how do I turn that on?

SET IDENTITY_INSERT tablename ON

Once you do that, all you have to do is list all the columns you want to insert to, and you can override the IDENTITY(1,1). You’ll still get an error if you try to insert a value that is already in use.  But otherwise, you can custom declare the values you want.

SET IDENTITY_INSERT development.yourDatabase.dbo.tablename ON

INSERT INTO development.yourDatabase.dbo.person
(personID, firstName, lastName)
SELECT *
FROM production.yourDatabase.dbo.person

That’s it!  Pretty easy right?  Well it is, as long as you don’t forget to turn IDENTITY_INSERT back OFF, when you’re done!

SET IDENTITY_INSERT tablename OFF

If you have any questions, send them in... I'm here to help!