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:
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
Well I hope this helps someone out there! 🙂
All questions and feedback are welcome.