IDENTITY_INSERT

Question

IDENTITY_INSERT

Steve Jones - SSC Editor

SSC Guru

Points: 736297
More actions
January 24, 2009 at 11:16 am

#205732

Comments posted to this topic are about the item IDENTITY_INSERT

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I think that the answer “You cannot set IDENTITY_INSERT on for more than one table in a database” should be changed to You cannot set IDENTITY_INSERT on for more than one table at the same time in a SESSION.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 2

Adi Cohn (1/26/2009)
I think that the answer “You cannot set IDENTITY_INSERT on for more than one table in a database” should be changed to You cannot set IDENTITY_INSERT on for more than one table at the same time in a SESSION.
Adi

To clarify further I would think

You cannot set IDENTITY_INSERT on for more than one table AT A TIME in a SESSION.

🙂

jmcleish SSC Rookie Points: 36 More actions · Answer 3

You want to correct a problem with two of your tables with keys created by identity values.

Personally I think this implies that there is erroneous data to be removed before an insert.

Inserting without deleting will simply add data without rectifying the problem. (generalisation)

Richard Barber Newbie Points: 9 More actions · Answer 4

Does anybody else but me think that Order_details is designed wrongly? OrderID should be a foreign key to Orders.OrderID. The natural key of Order_Details is OrderID and LineItemID which does not require an IDENTITY column at all.;)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

hmmm, over-think question we are. IDENTITY_INSERT is the heart of the question, not design of the tables.

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 6

Correcting the question now. good points about the session.

si_downes SSC Eights! Points: 920 More actions · Answer 7

Adi Cohn (1/26/2009)
I think that the answer “You cannot set IDENTITY_INSERT on for more than one table in a database” should be changed to You cannot set IDENTITY_INSERT on for more than one table at the same time in a SESSION.
Adi

true. very misleading

kapil_kk SSC-Insane Points: 21316 More actions · Answer 8

nice basic question 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Shiva N Hall of Fame Points: 3125 More actions · Answer 9

Thanks Dear,

It is really a good question for developers...

Thanks,
Shiva N
Database Consultant