Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Deadlock by getting Scope_identity()
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

35 Posts

Posted - 12/05/2013 :  23:27:14  Show Profile  Reply with Quote

I need a unique value for a composite key. I have created a little table that has an IDENTITY column. I inserts a record via StoredProc., And get as output parameters the SCOPE_IDENTITY ().
In a BulkInsert from a flat file, sometimes it comes at a high load to a DEADLOCK on this place..the procedure continues to run without having the key. Is there a Methode, where i can wait for a key ???
Is there a better method to get a UniqueKey quickly?


Edited by - gpc44 on 12/05/2013 23:32:32


United Kingdom
22859 Posts

Posted - 12/06/2013 :  03:26:03  Show Profile  Reply with Quote
Could you add an IDENTITY column to the actual table itself, and include that in your composite key instead?

It sounds like you are doing

INSERT INTO MyTable(Col1, Col2, ... ColID)
VALUES(@Col1, @Col2, ... @TheID)

and code from other places is also using the MySP_GetID, probably for other tables, and that is causing the deadlock (NOT the act of using Scope_Identity() )
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 12/06/2013 :  05:01:50  Show Profile  Reply with Quote
If its a bulk insert why should you do row by row insert? if your attempt is to get all generated id values you can use OUTPUT caluse for that. You dont need to make proc process row by row just for that.
the code will be like

INSERT YourTable (col1,col2,..)
SELECT value1,value2,..
FROM Source

if you want to capture it somewhere you can do that by adding the INTO part (commented above)

SQL Server MVP
Go to Top of Page

Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/06/2013 :  10:59:47  Show Profile  Reply with Quote
You are talking about composite keys and identities. Perhaps you should explain in more detail what you are doing or, even better, what you WANT to do.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000