SQL表内/表间数据复制
通常SQLServer在新增数据时，用insert into （f1,f2,f3…）values （v1,v2,v3…），但如果插入表的字段很多，写起来有点麻烦，可以通过动态方式去获取数据列并按条件新增插入。
SQL Server 2017以下版本：
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
— 获取除标识列外的所有列名
SELECT @columns = STUFF((
SELECT ‘,’ + QUOTENAME(c.name)
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
LEFT JOIN sys.identity_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE t.name = ‘tablename’ AND ic.object_id IS NULL
FOR XML PATH(”), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’), 1, 1, ”);
— 构建动态 SQL 语句
SET @sql = N’
INSERT INTO tablename (‘ + @columns + ‘)
SELECT ‘ + @columns + ‘
FROM tablename
WHERE fieldname = ”0001”;’;
— 执行动态 SQL
EXEC sp_executesql @sql;
SQL Server 2017及以上版本
SQL Server 2017版本以上支持 STRING_AGG 函数，提供了更高效的方法：
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
— 获取除标识列外的所有列名
SELECT @columns = STRING_AGG(QUOTENAME(c.name), ‘, ‘)
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
LEFT JOIN sys.identity_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE t.name = ‘tablename’ AND ic.object_id IS NULL;
— 构建动态 SQL 语句
SET @sql = N’
INSERT INTO tablename (‘ + @columns + ‘)
SELECT ‘ + @columns + ‘
FROM tablename
WHERE fieldname = ”0001”;’;
— 执行动态 SQL
EXEC sp_executesql @sql;