script to add and remove auto-increment property from a column

Question

For a sql script I'm working on, I need to programmatically remove the identity, identity seed, and identity increment for a column in an existing table, then add them back to the table at the end of the script. Does anyone have a reference or an example on how to do this?

Are you asking this because you need to insert some specific values into an identity column? — LittleBobbyTables - Au Revoir, Oct 01 '10 at 17:35

Ivan Ferić · Accepted Answer · 2017-03-28T09:00:45.273

45

You should do this:

SET IDENTITY_INSERT <TableName> ON
-- Do the inserting in the table with name <TableName>
SET IDENTITY_INSERT <TableName> OFF

For more details look in the MSDN.

edited Mar 28 '17 at 09:00

answered Oct 01 '10 at 17:37

Ivan Ferić

4,725
11
37
47

But where we added `column_name` on which we want to set `identity` ? @Ivan Ferić – Arsman Ahmad May 16 '18 at 11:45
You don't need to specify any column name - this will enable identity insert on the table – Ivan Ferić Mar 31 '20 at 13:04

Dustin Laine · Answer 2 · 2010-10-01T18:20:44.333

8

Yes, you just do this:

SET IDENTITY_INSERT [TABLE] ON

And then back on:

SET IDENTITY_INSERT [TABLE] OFF

This will allow you to enter manual data in the identity column.

http://msdn.microsoft.com/en-us/library/ms188059.aspx

edited Oct 01 '10 at 18:20

answered Oct 01 '10 at 17:35

Dustin Laine

37,935
10
86
125

4

You should first set it to ON, and in the end to OFF. With ON it signalizes that you are about to do manual inserts on identity column. – Ivan Ferić Oct 01 '10 at 17:40
Thanks, a little Friday morning aixelsyd. :) – Dustin Laine Oct 01 '10 at 17:44
1

You still have it the other way around. – Ivan Ferić Oct 01 '10 at 17:56

script to add and remove auto-increment property from a column

2 Answers2