Turn Identity Insert Off and On

  • Article

This is a really great thing to know if you are migrating data from one database to another and absolutely must retain the Id field of your records.  You can turn Indentity Insert Off / On so that you can specify the Id during insertion.

Don't get confused like I did though - it's reverse logic.  'On' doesn't mean that the Server is maintaining Identity, it means that you are.  And make sure you set it back to 'Off' after your insertions.

SET IDENTITY_INSERT yourtable ON

SET IDENTITY_INSERT yourtable OFF