SQL Server中同表复制数据并同步更新单列的最优实现方法

需求说明 #

• 同表复制现有行并插入回原表，仅对新插入的行更新某一指定列的值，用于多客户业务自动化脚本开发
• 无法直接使用SELECT *语法，表中存在identity自增列会触发报错，同时不希望为多个表手动逐一编写全部列名

实现代码（采纳的临时表方案） #

该方案无需手动枚举列名，执行效率高，适配多表批量调用场景：

-- 配置参数，使用时修改以下3个变量即可
DECLARE @TableName NVARCHAR(128) = N'你的业务表名'
DECLARE @TargetCol NVARCHAR(128) = N'需要更新的列名'
DECLARE @TargetVal NVARCHAR(MAX) = N'新插入行的列值' -- 可根据列实际类型调整数据类型

-- 拼接动态SQL，自动排除自增列
DECLARE @ExecSQL NVARCHAR(MAX)
SELECT @ExecSQL = N'
-- 拷贝原表数据到临时表，排除自增列
SELECT ' + STRING_AGG(QUOTENAME(name), N', ') + N' INTO #TempData FROM ' + QUOTENAME(@TableName) + N'
-- 临时表更新指定列
UPDATE #TempData SET ' + QUOTENAME(@TargetCol) + N' = @ParamVal
-- 数据插回原表，自增列自动生成新值
INSERT INTO ' + QUOTENAME(@TableName) + N' (' + STRING_AGG(QUOTENAME(name), N', ') + N')
SELECT * FROM #TempData
DROP TABLE #TempData
'
FROM sys.columns 
WHERE object_id = OBJECT_ID(@TableName) AND is_identity = 0

-- 执行SQL
EXEC sp_executesql @ExecSQL, N'@ParamVal NVARCHAR(MAX)', @ParamVal = @TargetVal

补充说明 #

• 如果只需要复制原表的部分行，可在FROM ' + QUOTENAME(@TableName) + N'后添加WHERE筛选条件
• 若要更新的列为数值、日期等非字符串类型，同步调整@TargetVal和@ParamVal的定义类型即可
• SQL Server 2017及以上版本支持STRING_AGG函数，更低版本可替换为XML路径拼接列名的写法

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