在链接的SQL Server中插入ID [英] Inserted Id in linked SQL Server

问题描述

我已经将2台Microsoft SQL服务器链接在一起，我们将它们称为LinkedServer1和Server2.我正在尝试使用带有OUTPUT的INSERT脚本从第二个数据中插入数据，以获取自动递增的ID:

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:

在包含OUTPUT子句或嵌套DML语句的语句中，不能将远程表用作DML目标.

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

如何将值插入链接的SQL Server表中并获取新的ID?

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

推荐答案

如果从 LinkedServer1 可见链接服务器 Server2 ，则可以执行以下操作:

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

这篇关于在链接的SQL Server中插入ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！