如何让IDENTITY列同时支持自动赋值与手动插入？

核心问题在于SQL Server的语法限制：当IDENTITY_INSERT设为OFF时，不能在INSERT语句中显式指定IDENTITY列（哪怕传入值是NULL），这就是你触发544错误的原因。要同时支持自动生成和手动插入两种场景，需要根据传入ID是否为NULL动态调整逻辑，结合IDENTITY_INSERT的开关来实现。

方法1：动态SQL实现兼容INSERT #

通过构建动态SQL语句，根据临时表中的ID状态，决定是否包含IDENTITY列在INSERT字段列表中，同时控制IDENTITY_INSERT的开关：

-- Create original table with existing sample data
CREATE TABLE #o 
(
    [Id] int identity(10, 1),
    [First Name] varchar(50),
    [Last Name] varchar(50)
)

INSERT INTO #o ([First Name], [Last Name])
    SELECT *
    FROM (
        VALUES ('Dwight', 'Schrute'), ('Jim', 'Halpert')) x(a, b)

-- Create a table to supply a row of data where the Id could be NULL or a unique value
DECLARE @id int = NULL -- 可切换为具体数值测试手动插入场景

SELECT *
INTO #t
FROM (
    VALUES(@id, 'Michael', 'Scott')
    ) t([Id], [First Name], [Last Name])

-- 声明动态SQL变量
DECLARE @sql NVARCHAR(MAX)
DECLARE @identityInsert NVARCHAR(100)

-- 判断是否需要开启IDENTITY_INSERT
IF EXISTS (SELECT 1 FROM #t WHERE [Id] IS NOT NULL)
BEGIN
    SET @identityInsert = 'SET IDENTITY_INSERT #o ON;'
    -- 构建包含ID列的INSERT语句
    SET @sql = @identityInsert + N'
    INSERT INTO #o ([Id], [First Name], [Last Name])
    SELECT #t.[Id], #t.[First Name], #t.[Last Name]
    FROM #t
    LEFT JOIN #o ON #o.[Id] = #t.[Id]
    WHERE #o.[Id] IS NULL;
    SET IDENTITY_INSERT #o OFF;'
END
ELSE
BEGIN
    -- 构建不包含ID列的INSERT语句，让IDENTITY自动生成
    SET @sql = N'
    INSERT INTO #o ([First Name], [Last Name])
    SELECT #t.[First Name], #t.[Last Name]
    FROM #t
    LEFT JOIN #o ON #o.[Id] = #t.[Id]
    WHERE #o.[Id] IS NULL;'
END

-- 执行动态SQL
EXEC sp_executesql @sql

-- 查看结果
SELECT * FROM #o ORDER BY [Id]

DROP TABLE #t
DROP TABLE #o

方法2：用MERGE实现UPSERT（更适配你的场景） #

因为你提到这是UPSERT（存在则更新，不存在则插入）逻辑的一部分，使用MERGE语句可以更简洁地整合两种场景：

-- Create original table with existing sample data
CREATE TABLE #o 
(
    [Id] int identity(10, 1) PRIMARY KEY,
    [First Name] varchar(50),
    [Last Name] varchar(50)
)

INSERT INTO #o ([First Name], [Last Name])
    SELECT *
    FROM (
        VALUES ('Dwight', 'Schrute'), ('Jim', 'Halpert')) x(a, b)

-- Create a table to supply a row of data where the Id could be NULL or a unique value
DECLARE @id int = 4 -- 可切换为NULL测试自动生成场景

SELECT *
INTO #t
FROM (
    VALUES(@id, 'Michael', 'Scott')
    ) t([Id], [First Name], [Last Name])

DECLARE @identityInsert NVARCHAR(100) = ''

-- 判断是否需要开启IDENTITY_INSERT
IF EXISTS (SELECT 1 FROM #t WHERE [Id] IS NOT NULL)
BEGIN
    SET @identityInsert = 'SET IDENTITY_INSERT #o ON;'
END

-- 构建MERGE的动态SQL
DECLARE @sql NVARCHAR(MAX) = @identityInsert + N'
MERGE #o AS target
USING #t AS source
ON target.[Id] = source.[Id]
WHEN MATCHED THEN
    UPDATE SET 
        [First Name] = source.[First Name],
        [Last Name] = source.[Last Name]
WHEN NOT MATCHED THEN
    INSERT ' + CASE WHEN EXISTS(SELECT 1 FROM #t WHERE [Id] IS NOT NULL) THEN '([Id], [First Name], [Last Name])' ELSE '([First Name], [Last Name])' END + N'
    VALUES (' + CASE WHEN EXISTS(SELECT 1 FROM #t WHERE [Id] IS NOT NULL) THEN 'source.[Id], ' ELSE '' END + N'source.[First Name], source.[Last Name]);'

-- 如果开启了IDENTITY_INSERT，执行后关闭
IF @identityInsert <> ''
BEGIN
    SET @sql = @sql + N' SET IDENTITY_INSERT #o OFF;'
END

-- 执行动态SQL
EXEC sp_executesql @sql

-- 查看结果
SELECT * FROM #o ORDER BY [Id]

DROP TABLE #t
DROP TABLE #o

关键说明 #

1. 为什么无法用单一静态INSERT？
   SQL Server的语法规则强制：当IDENTITY_INSERT为OFF时，任何显式提及IDENTITY列的INSERT操作（哪怕值为NULL）都会触发错误，因此必须通过动态SQL分支处理。
2. 重复ID的保障
   你提到程序会提前验证ID唯一性，且ID是主键，数据库会自动拦截重复插入，无需额外逻辑处理。
3. MERGE的优势
   直接将UPSERT逻辑整合到一个语句中，避免了多次判断分支的繁琐，更适合嵌入存储过程使用。

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