How to insert specific values into a SQL table with an Identity column


As you probably already know; the values in an Identity column are auto-generated by the database, and as such you can’t just insert a specific value into those columns.

Normally if you try to insert a specific values into an Identity column you will get an error similar to this:

Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.

Well sometimes you really do want to use specific values for the Id. For example you may want to pre-populate a database or you need to fix some data, or a hundred other reasons. Well this is how you do it:

SET IDENTITY_INSERT [dbo].[Customer] ON -- This turns auto-generated values off

INSERT INTO [dbo].[Customer] (Id, [Name]) VALUES (123, 'Gordon') --Insert your rows

SET IDENTITY_INSERT [dbo].[Customer] OFF -- This turns auto-generated values back on

The Results:
The results

Well I hope this helps someone out there! 🙂

All questions and feedback are welcome.

http://msdn.microsoft.com/en-ie/library/ms186775.aspx

MSDN Documentation

Advertisements