Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IDENTITY_INSERT Expand / Collapse
Author
Message
Posted Saturday, January 24, 2009 11:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:24 AM
Points: 33,088, Visits: 15,197
Comments posted to this topic are about the item IDENTITY_INSERT






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #643010
Posted Monday, January 26, 2009 1:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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/
Post #643292
Posted Monday, January 26, 2009 8:19 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
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.


:)

Post #643456
Posted Monday, January 26, 2009 3:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 1, 2011 5:56 PM
Points: 28, Visits: 56

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)
Post #643745
Posted Monday, January 26, 2009 8:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 21, 2010 12:32 AM
Points: 1, Visits: 14
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.;)
Post #643868
Posted Monday, January 26, 2009 9:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 23,042, Visits: 31,566
hmmm, over-think question we are. IDENTITY_INSERT is the heart of the question, not design of the tables.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #643888
Posted Tuesday, January 27, 2009 9:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:24 AM
Points: 33,088, Visits: 15,197
Correcting the question now. good points about the session.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #644245
Posted Wednesday, March 18, 2009 10:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 6:47 AM
Points: 426, Visits: 217
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
Post #678701
Posted Monday, January 28, 2013 5:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
nice basic question


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1412358
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse