Inserted Id in linked SQL Server

Question

I have linked together 2 Microsoft SQL servers, let's call them LinkedServer1 and Server2. I am trying to insert data from the second one using an INSERT script with OUTPUT to obtain the auto-incremented id:

DECLARE @newIdHolderTable TABLE (Id INT)

INSERT INTO [LinkedServer1].[Db1].[dbo].[Table1]
  (Field1
  ,Field2)
OUTPUT inserted.ID INTO @newIdHolderTable 
SELECT 
    Field1
   ,Field2
FROM [Server2].[Db1].[dbo].[Table1]
WHERE [Id] = @Id

Unfortunately this fails with an error message:

A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

How can I insert values into a linked SQL server table and obtain the new Id?

score 2 · Answer 1 · answered Sep 24 '13 at 12:26

If your linked server Server2 is visible from LinkedServer1, you can do this:

declare @newIdHolderTable table (Id INT);

declare @stmt nvarchar(max)

select @stmt = '
  INSERT [Db1].[dbo].[Table1] (Field1,Field2) VALUES (values);
  OUTPUT inserted.ID
  SELECT 
      Field1
     ,Field2
  FROM [Server2].[Db1].[dbo].[Table1]
  WHERE [Id] = @Id
'

insert into @newIdHolderTable
exec LinkedServer1.master..sp_executesql
    @stmt = @stmt,
    @params = N'@Id int',
    @Id = @Id

Inserted Id in linked SQL Server

1 Answers1

Linked