SQL Server中使用表变量向现有表插入数据时自增主键问题求助

看起来你在使用表变量往带自增主键的SQL Server表插入数据时碰了壁，我来帮你捋清楚问题所在并给出解决方案！

先修正一下你提供的表结构（看起来列顺序有点小问题，调整成正确形式方便后续说明）：

修正后的表结构 #

State表（自增主键ID_State） #

City表（自增主键ID_city） #

问题根源 #

SQL Server的自增主键（IDENTITY属性列）默认由系统自动生成唯一值，不允许用户显式赋值——除非你特意开启IDENTITY_INSERT选项。如果你的表变量包含了自增主键列，插入时又没做对应设置，就会触发类似这样的错误：

当IDENTITY_INSERT设置为OFF时，不能为表'City'中的标识列插入显式值。

两种解决方案 #

方案1：表变量不包含自增主键列（推荐，适合常规插入） #

如果不需要手动指定自增ID，直接在表变量里去掉自增列，插入时让SQL Server自动生成值即可：

-- 定义表变量，只包含需要插入的业务列
DECLARE @CitiesToInsert TABLE (
    City NVARCHAR(100),
    ID_State INT
);

-- 往表变量中添加待插入的数据
INSERT INTO @CitiesToInsert (City, ID_State)
VALUES 
    ('Hyderabad', 4),
    ('Ahmedabad', 5);

-- 插入到City表，无需指定自增列ID_city，数据库会自动生成
INSERT INTO City (City, ID_State)
SELECT City, ID_State 
FROM @CitiesToInsert;

方案2：显式指定自增主键值（适合数据迁移/保留原有ID场景） #

如果必须要手动设置自增ID的值，需要先开启IDENTITY_INSERT，插入完成后记得关闭：

-- 开启IDENTITY_INSERT，允许对City表的自增列显式赋值
SET IDENTITY_INSERT City ON;

-- 定义包含自增列的表变量
DECLARE @CitiesToInsert TABLE (
    ID_city INT,
    City NVARCHAR(100),
    ID_State INT
);

-- 添加带指定ID的数据到表变量
INSERT INTO @CitiesToInsert (ID_city, City, ID_State)
VALUES 
    (4, 'Hyderabad', 4),
    (5, 'Ahmedabad', 5);

-- 插入时必须显式列出所有列（包括自增列），不能用SELECT *
INSERT INTO City (ID_city, City, ID_State)
SELECT ID_city, City, ID_State 
FROM @CitiesToInsert;

-- 关闭IDENTITY_INSERT，恢复默认行为
SET IDENTITY_INSERT City OFF;

注意事项 #

• IDENTITY_INSERT同一时间只能对一个表开启，不能同时给多个表启用
• 开启后插入操作必须显式指定列名，不能省略（否则SQL Server无法匹配自增列的赋值）
• 手动指定的自增ID不能和表中已有的ID重复，否则会触发主键冲突错误

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