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.

MSDN Documentation


Comments - Have your say ;-)

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s