Sql-server – Insert data through linked server into two remote related tables

sql server

I want insert data through linked server into two remote related tables as below:

(SQL 2008R2 on both machines)

SERVER_AAA:

create table #server_AAA_table_1
(
        [var_1] [int] NULL,
        [var_2] [bigint] NULL,
        [var_3] [varchar](64) NULL
)


create table #server_AAA_table_2
(
        [var_4] [char](1)NULL,
        [var_5] [int] NULL,
        [var_6] [int] NULL,
        [var_7] [int] NULL,
        [id_1] [int]  NULL
)

SERVER_BBB:

create table server_BBB_table_1
(
        [id_B_1] [int] IDENTITY(1,1) NOT NULL,
        [var_1] [int] NULL,
        [var_2] [bigint] NULL,
        [var_3] [varchar](64) NULL,
constraint [PK_server_BBB_table_1] primary key CLUSTERED ([id_B_1] ASC)
) 



create table server_BBB_table_2
(
        [id_B_2] [int] IDENTITY(1,1) NOT NULL,
        [var_4] [char](1)NULL,
        [var_5] [int] NULL,
        [var_6] [int] NULL,
        [var_7] [int] NULL,
        [id_B_1] [int] NULL,
constraint [PK_server_BBB_table_2] primary key CLUSTERED ([id_B_2] ASC)
)

alter table server_BBB_table_2 with check add  constraint [FK_server_BBB_table_2_server_BBB_table_1] foreign key([id_B_1])
references server_BBB_table_1([id_B_1])
on update cascade
on delete cascade
go

SERVER_AAA:

Linked server: LNK_AAA_2_BBB

declare @id_1 int   

insert into LNK_AAA_2_BBB.server_BBB_DB.server_BBB_table_1 select * from #server_AAA_table_1

set @id_1 = scope_identity();

/*
 some sql code: @id_1 + #server_AAA_table_2
*/

insert into LNK_AAA_2_BBB.server_BBB_DB.server_BBB_table_2 select * from #server_AAA_table_2

Unfortunately, in any way I can't get scope_identity() value from first insert, I always get NULL.

I tried solution with OUTPUT and procedure, no success.

Is this possible to do via linked server?
Maybe I need some other solution?

Best Answer

You have to use sp_executesql or openquery ..

E.g. pseudo code .. 

declare @remoteIdentity (ID int)
insert into @remoteIdentity 
EXEC LNK_AAA_2_BBB.master..sp_executesql N 'select * from #server_AAA_table_1
select SCOPE_IDENTITY() AS ID';
SELECT * FROM @remoteIdentity;

Read up : Best way to get identity of inserted row?

Related Question