Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Identity insert

Tags:

sql-server

can anybody please, explain that what does SET IDENTITY INSERT ON AND OFF do.

Thanks, Chris

like image 752
user47957 Avatar asked Jan 04 '09 22:01

user47957


3 Answers

Suppose you have a table with an auto-increment/identity column. You normally cannot specify values for this column when you do inserts since it is automatically populated.

However, if you call SET IDENTITY_INSERT YourTable ON first, you can insert specific values into the identity column.

I use this most often when merging tables manually--I've never used it in a regularly executed block of code.

Also note that you can only specify this on a single table at a time.

like image 128
Michael Haren Avatar answered Sep 18 '22 16:09

Michael Haren


SET IDENTITY_INSERT ON

Allows explicit values to be inserted into the identity column of a table.

like image 20
Christian C. Salvadó Avatar answered Sep 16 '22 16:09

Christian C. Salvadó


Michael Haren has given you the answer, but I want to amplify. It is exceedingly dangerous to use this command and you should not use it except in a few very limited cases. When you use it the autonumbering no longer works for anyone, so all code to insert records normally into the database will fail. It therefor should be done in single user mode whenever possible or at the very least during the off hours. It should not be done to regularly insert records but only for things such as merging data from one table to a new structure in a database redesign. If you do not know what you are doing when you use this command you can royally mess up the data integrity of a database.

like image 41
HLGEM Avatar answered Sep 19 '22 16:09

HLGEM