如何在SQL Server中实现带有排序逻辑的INSERT_结合OVER子句与IDENTITY-SQL-PHP中文网

SQL Server中IDENTITY列不能在INSERT...SELECT中直接用OVER子句生成值，必须先SET IDENTITY_INSERT ON才能显式插入ROW_NUMBER()等计算值，且需及时关闭；更优方案是改用SEQUENCE配合DEFAULT约束。

如何在sql server中实现带有排序逻辑的insert_结合over子句与identity

SQL Server不支持INSERT ... SELECT中直接用OVER子句生成IDENTITY值

IDENTITY列的值由SQL Server引擎自动管理，不能在INSERT语句里通过ROW_NUMBER() OVER (...)等窗口函数“手动指定”并期望它被写入IDENTITY列——这会触发错误：Cannot insert explicit value for identity column in table 'xxx' when IDENTITY_INSERT is OFF。哪怕你用SELECT ROW_NUMBER() OVER (ORDER BY ...)构造了序号，只要目标列是IDENTITY且没开IDENTITY_INSERT，就必然失败。

正确做法：先用CTE或子查询排序编号，再配合SET IDENTITY_INSERT ON

想按特定顺序（比如按created_time DESC）控制新插入记录在IDENTITY列上的逻辑序号，必须分两步走：

用ROW_NUMBER() OVER (ORDER BY created_time DESC)等生成临时序号（注意：这个序号只是中间值，不是最终IDENTITY值）
显式开启IDENTITY_INSERT，把该序号作为显式值插入到IDENTITY列
插入完成后立即关闭IDENTITY_INSERT，避免影响后续常规插入

示例：

SET IDENTITY_INSERT dbo.orders ON;
INSERT INTO dbo.orders (id, customer_id, amount)
SELECT rn, customer_id, amount
FROM (
  SELECT customer_id, amount,
         ROW_NUMBER() OVER (ORDER BY created_time DESC) AS rn
  FROM staging_orders
) t;
SET IDENTITY_INSERT dbo.orders OFF;

IDENTITY_INSERT只能对单个表启用，且当前会话独占

这是常被忽略的硬约束：

SET IDENTITY_INSERT table_name ON只对当前会话生效，其他连接不受影响
同一时间，一个会话最多只能对一个表启用IDENTITY_INSERT；试图对第二个表执行ON会报错：IDENTITY_INSERT is already ON for table 'xxx'
如果插入中途出错，务必确保SET IDENTITY_INSERT ... OFF被执行，否则后续对该表的普通插入会失败
不建议在存储过程中长期开启，应严格配对使用