SSMS中批量复制SQL表数据前校验缺失字段并动态建表的咨询

前置说明 #

请先确保你已经提前创建了字段配置表TABLE_COL_NAME，包含两个核心字段：COL_NAME（存储要提取的字段名，比如x、y、z）、COL_TYPE（存储对应字段的类型，比如int、varchar(200)）。

完整适配代码（兼容SSMS v18.7、SQL Server 2016+环境） #

DECLARE 
 @TableName VARCHAR(500)
,@Sql NVARCHAR(MAX) = ''
,@Id INT
,@ColDef NVARCHAR(MAX) = '' -- 动态生成目标表的字段定义
,@ColList NVARCHAR(MAX) = '' -- 动态生成查询字段模板

-- 第一步：从配置表读取字段，生成建表语句和通用字段模板
SELECT 
  @ColDef += QUOTENAME(COL_NAME) + ' ' + COL_TYPE + ',',
  @ColList += QUOTENAME(COL_NAME) + ','
FROM TABLE_COL_NAME
-- 去除拼接后最后多余的逗号
SET @ColDef = LEFT(@ColDef, LEN(@ColDef)-1)
SET @ColList = LEFT(@ColList, LEN(@ColList)-1)

-- 第二步：自动创建目标表结构
DROP TABLE IF EXISTS TABLE_NEW
EXEC ('CREATE TABLE TABLE_NEW (' + @ColDef + ')')

-- 第三步：遍历所有ABC开头的源表
DECLARE Table_Cursor CURSOR 
FOR 
SELECT 
ROW_NUMBER() OVER (ORDER BY TABLE_NAME ASC) Id
,TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
AND TABLE_NAME LIKE 'ABC%'

OPEN Table_Cursor  
FETCH NEXT FROM Table_Cursor INTO @Id,@TableName 

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- 针对当前源表适配字段：不存在的字段自动补默认值'0'
    DECLARE @CurrCol NVARCHAR(MAX) = ''
    SELECT @CurrCol += 
        CASE WHEN c.COLUMN_NAME IS NULL THEN '''0'' AS ' + QUOTENAME(t.COL_NAME) 
             ELSE QUOTENAME(c.COLUMN_NAME) END + ','
    FROM TABLE_COL_NAME t
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS c 
        ON c.TABLE_NAME = @TableName 
        AND c.COLUMN_NAME = t.COL_NAME
    SET @CurrCol = LEFT(@CurrCol, LEN(@CurrCol)-1)

    -- 拼接查询逻辑
    IF(@Id = 1)
    BEGIN
        SET @Sql = @Sql + 'SELECT ' + @CurrCol + ' FROM ' + QUOTENAME(@TableName)
    END
    ELSE
    BEGIN
        SET @Sql = @Sql + ' UNION ALL SELECT ' + @CurrCol + ' FROM ' + QUOTENAME(@TableName)
    END

    FETCH NEXT FROM Table_Cursor INTO @Id,@TableName
END 

CLOSE Table_Cursor  
DEALLOCATE Table_Cursor

-- 执行批量插入
INSERT INTO TABLE_NEW 
EXEC (@Sql)

核心适配点说明 #

• 字段缺失自动兼容：遍历每个源表时会自动匹配配置的字段列表，不存在的字段自动返回'0'作为默认值，覆盖所有字段缺失的组合场景
• 全动态适配配置：目标表结构、提取字段列表完全从TABLE_COL_NAME读取生成，后续调整提取字段时只需要修改配置表的记录即可，无需改动代码逻辑
• 鲁棒性优化：使用QUOTENAME包裹表名、字段名，避免特殊字符或关键字导致的语法错误，修复了原代码字段拼接缺少空格的语法问题

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