CODEWITHSUNDEEP
sql-servert-sqlidentity
Santa Paws
Santa Paws

Reputation: 60

How can I reliably get identity ID of a row inserted into a different database

Is scope_identity() a reliable way to get the identity ID of a row newly inserted into a different database on the same SQL server?

I have two databases DB_A and DB_B sitting on the same SQL server (2012) I have a stored proc residing in DB_A, in which a row is inserted into DB_B. The identity ID of this row needs to be obtained to continue the stored proc.

   insert into DB_B.dbo.CustAddress values (@newAddress);

   select @newID = scope_identity()

I have tested this approach on my test server and it works just fine. However, it seems that sometimes (rare and hard to catch) the ID is not obtained. The ID is simply 0 (not like returning a wrong ID) on production server, which is a busy environment with lots of insertion into DB_B from various sources. Since there is no ID, I cannot verify if the insertion is completed successfully, though there is no SQL exception.

My question is that can I trust the returned ID and under what circumstances will SQL fails returning the identity ID of the newly inserted row in this way?

Upvotes: 1

Views: 262

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Use the output clause:

declare table @ids (id int);
insert into DB_B.dbo.CustAddress
    output inserted.id into @ids
    values (@newAddress);

select *
from @ids;

Upvotes: 4

Related Questions