动态SQL中通过链接服务器用SET IDENTITY_INSERT跨服务器插同ID记录

咱们先理清楚核心需求：要在两台服务器的同结构带标识列的表中插入同一条记录，得自己控制统一ID，还要用动态SQL来灵活适配不同的服务器、数据库和表。下面给你一套落地的解决方案：

1. 核心思路拆解 #

因为两张表都是IDENTITY标识列，默认会自动生成ID，所以必须先临时关闭标识列的自动增长，插入咱们生成的统一ID后再恢复；另外要保证获取最大ID的操作是原子性的，避免并发场景下多个请求拿到重复ID。

2. 完整动态SQL实现 #

DECLARE @ServerA NVARCHAR(128) = N'SERVER_A'
DECLARE @ServerB NVARCHAR(128) = N'SERVER_B'
DECLARE @DBName NVARCHAR(128) = N'MyDB'
DECLARE @TableName NVARCHAR(128) = N'MyTable'
DECLARE @NewID INT
DECLARE @SQL NVARCHAR(MAX)

-- 步骤1：获取全局唯一的新ID（取两台服务器中最大的ID加1，避免冲突）
SET @SQL = N'
SELECT @NewID = MAX(ID) + 1
FROM (
    SELECT ID FROM [' + @ServerA + N'].[' + @DBName + N'].dbo.[' + @TableName + N']
    UNION ALL
    SELECT ID FROM [' + @ServerB + N'].[' + @DBName + N'].dbo.[' + @TableName + N']
) AS CombinedIDs'

EXEC sp_executesql @SQL, N'@NewID INT OUTPUT', @NewID OUTPUT

-- 步骤2：开启事务，保证两台服务器的插入操作要么都成功要么都失败
BEGIN TRANSACTION
BEGIN TRY
    -- 动态构建SERVER_A的插入SQL（先开启IDENTITY_INSERT）
    SET @SQL = N'
    USE [' + @ServerA + N'].[' + @DBName + N']
    SET IDENTITY_INSERT dbo.[' + @TableName + N'] ON
    INSERT INTO dbo.[' + @TableName + N'] (ID, Column1, Column2) -- 替换成你的实际列名
    VALUES (@NewID, @Val1, @Val2) -- 替换成你的实际业务值
    SET IDENTITY_INSERT dbo.[' + @TableName + N'] OFF'

    EXEC sp_executesql @SQL, N'@NewID INT, @Val1 VARCHAR(50), @Val2 INT', -- 替换成你的参数类型
        @NewID = @NewID, @Val1 = '测试内容', @Val2 = 100 -- 替换成你的实际参数值

    -- 动态构建SERVER_B的插入SQL
    SET @SQL = N'
    USE [' + @ServerB + N'].[' + @DBName + N']
    SET IDENTITY_INSERT dbo.[' + @TableName + N'] ON
    INSERT INTO dbo.[' + @TableName + N'] (ID, Column1, Column2) -- 替换成你的实际列名
    VALUES (@NewID, @Val1, @Val2) -- 替换成你的实际业务值
    SET IDENTITY_INSERT dbo.[' + @TableName + N'] OFF'

    EXEC sp_executesql @SQL, N'@NewID INT, @Val1 VARCHAR(50), @Val2 INT', -- 替换成你的参数类型
        @NewID = @NewID, @Val1 = '测试内容', @Val2 = 100 -- 替换成你的实际参数值

    COMMIT TRANSACTION
    PRINT '插入成功，新ID：' + CAST(@NewID AS VARCHAR)
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    PRINT '插入失败：' + ERROR_MESSAGE()
END CATCH

3. 关键注意事项 #

• 并发冲突规避：如果有多个请求同时执行，直接取MAX(ID)+1可能会撞ID。推荐改用SEQUENCE序列对象（SQL Server 2012+支持），在其中一台服务器创建全局序列，两台服务器都从这个序列取ID，能保证原子性和唯一性：

  -- 在SERVER_A创建全局序列
  CREATE SEQUENCE dbo.MyTableIDSequence
  AS INT
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 999999
  CYCLE NO
  CACHE 10;
  
  -- 取新ID的方式替换为
  SET @SQL = N'SELECT @NewID = NEXT VALUE FOR [' + @ServerA + N'].[' + @DBName + N'].dbo.MyTableIDSequence'
  EXEC sp_executesql @SQL, N'@NewID INT OUTPUT', @NewID OUTPUT
  

• 链接服务器配置：确保SERVER_A已经配置了到SERVER_B的链接服务器，否则无法直接访问[SERVER_B]的表，可通过sp_addlinkedserver命令配置。
• 权限要求：执行脚本的账号需要拥有两台服务器的ALTER TABLE（开启IDENTITY_INSERT需要）、INSERT以及表读取权限。
• 防注入处理：如果@ServerA、@DBName这些变量是用户输入的，一定要用QUOTENAME()函数转义，避免SQL注入风险：

  SET @ServerA = QUOTENAME(@ServerA)
  SET @DBName = QUOTENAME(@DBName)
  SET @TableName = QUOTENAME(@TableName)
  

内容的提问来源于stack exchange，提问作者carlosm