How to get identity ID in different database?

Question

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?

Are the looking at something like this http://stackoverflow.com/questions/4537560/get-identity-from-another-serverlinked-server — Praveen, May 09 '12 at 19:03
You code sets `@id2` to last inserted identity of `DB2.dbo.tab2`. What is actually the problem? — wqw, May 10 '12 at 08:00

Chris Moutray · Answer 1 · 2012-05-09T20:02:24.180

2

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.

edited May 09 '12 at 20:02

answered May 09 '12 at 19:24

Chris Moutray

Just be careful about assignment to a variable - if the source of the insert is a `SELECT` or `VALUES(),(),()` then you are only getting one of the rows assigned to the variable. – Aaron Bertrand May 09 '12 at 19:39
I got an error like: Msg 405, Level 16, State 1, Procedure SP_XYZ, A remote table cannot be used as a DML target in a statement which includes an `OUTPUT` clause or a nested DML statement. – Jaider May 23 '14 at 13:57

1 Answers1