Inserted Id In Linked Sql Server

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 obta

Solution 1:

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