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!
Original Article