0

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?

2

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?

1
  • I tried with OPENQUERY and sp_execute, unfortunately it looks like that "insert into LNK_AAA_2_BBB.server_BBB_DB.server_AAA_table_1 selelct * from #server_AAA_table_1" does not work in case insert from temp table. ps. I read this post.
    – 2k8MKS
    Nov 17 '17 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.