5

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

Thanks, Chris

Improve this question
4
  • -1 this question is at the end of the F1 key. Commented Jan 4, 2009 at 23:30
  • Actually it's SET IDENTITY INSERT [tablename] ON/OFF. Commented Jan 4, 2009 at 23:58
  • Actually @NeilBarnwell it's SET IDENTITY_INSERT [tablename] ON/OFF Commented Oct 12, 2018 at 15:00
  • Fair point. I should have made it clearer that what I was pointing out is that it's per-table, so the table name argument is required. And I nearly got 10 years without anyone noticing my typo. Oh well. Commented Oct 12, 2018 at 16:28

4 Answers 4

Reset to default
7

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

SET IDENTITY_INSERT ON

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

Improve this answer

Comments

3

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.

Improve this answer

Comments

-1

chrisprat, so far you've been registered for a week, and all your activity has been to ask five questions that are easily answered by either Google or MSDN.

What's up?

Improve this answer

2 Comments

@ceejayoz - I wonder why you felt the need to be rude. It's unecessary and unfunny, where does it get us?
Apparantly stackoverflow is better for chrisprat than google, if it's where he asks the questions first? Others are free not to answer, as they are also free to turn of the TV when nothing is on.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.