CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
KentZhou
KentZhou

Reputation: 25573

How to get identity ID in different database?

Suppose I have 2 databases DB1, DB2 under same instance. I have 2 tables in each database like:

tab1(id1, ...): --id1 is identity column in DB1
tab2(id2,...) : --id2 is identity column in DB2

I have following codes in DB1 SP:

use DB1
declare @id2 int;
insert into tab1(...) values (...);
insert into DB2.dbo.tab2(...) values (...);
set @id2 = SCOPE_IDENTITY();

but I can't get the right value for id2, it is null. How to resolve this problem?

Upvotes: 0

Views: 1755

Answers (1)

Chris Moutray
Chris Moutray

Reputation: 18379

Taking a different approach you could try and use the 'output' clause as part of the insert.

declare @output table (id int)

insert into DB2.dbo.tab2 (...)
output inserted.id2
into @output
values (...)

select @id2 = id from @output

I'm pretty sure this is the syntax for output but I've not tested it.

Upvotes: 2

Related Questions