带数据类型转换的动态SQL跨表插入失败问题排查

有两张表结构如下：

-- table_A
CREATE TABLE table_A
( 
  col_a varchar(100),
  col_b bigint,
  col_c datetime
)

-- table_B（字段名与table_A一致，但类型不同）
CREATE TABLE table_B
(
  col_a varchar(10),
  col_b varchar(10),
  col_c varchar(20)
)

需要将table_B的数据插入table_A并做数据类型转换，静态SQL执行正常：

INSERT INTO table_A(col_a,col_b,col_c)
SELECT CONVERT(varchar,col_a),CONVERT(INT,col_b),CONVERT(datetime,col_c) FROM table_B

尝试借助INFORMATION_SCHEMA.COLUMNS生成动态SQL，但执行后table_A无数据插入，核心问题在于CASE语句生成的CONVERT字符串有误，以下是解决思路和修正方案：

1. CONVERT语法完全错误：原CASE生成的是CONVERT('目标类型','源类型')，但正确的CONVERT语法应为CONVERT(目标类型, 源字段名)，参数顺序颠倒且错误用源类型替代了字段名。
2. INFORMATION_SCHEMA关联逻辑错误：原查询中FROM INFORMATION_SCHEMA S应为FROM INFORMATION_SCHEMA.COLUMNS S，且表名匹配逻辑混乱，未明确指定源表和目标表。
3. 字段拼接缺少分隔符：循环拼接时未添加逗号，会生成语法错误的字段列表（如col_acol_bcol_c）。
4. Synapse SQL ID不连续问题：依赖IDENTITY的WHILE循环会跳过不连续的ID，导致字段遗漏。

步骤1：创建临时表并正确关联字段映射 #

CREATE TABLE #TempTable
(
  ID INT IDENTITY(1,1),
  Src_Col VARCHAR(100),
  Src_dtype VARCHAR(50),
  Dest_Col VARCHAR(100),
  Dest_dtype VARCHAR(50),
  Modified_Col VARCHAR(200)
)

INSERT INTO #TempTable(Src_Col, Src_dtype, Dest_Col, Dest_dtype, Modified_Col)
SELECT 
  S.COLUMN_NAME AS Src_Col, 
  S.DATA_TYPE AS Src_dtype,
  D.COLUMN_NAME AS Dest_Col,
  D.DATA_TYPE AS Dest_dtype,
  -- 正确生成转换语句：目标类型在前，源字段名在后
  CASE 
    WHEN S.DATA_TYPE != D.DATA_TYPE THEN CONCAT('CONVERT(', D.DATA_TYPE, ', ', S.COLUMN_NAME, ')')
    ELSE S.COLUMN_NAME
  END AS Modified_Col
FROM INFORMATION_SCHEMA.COLUMNS S
JOIN INFORMATION_SCHEMA.COLUMNS D
  ON S.COLUMN_NAME = D.COLUMN_NAME
WHERE 
  S.TABLE_NAME = 'table_B'
  AND D.TABLE_NAME = 'table_A'

步骤2：高效拼接字段列表（适配Synapse SQL） #

使用STRING_AGG直接聚合字段，避免循环遍历的问题：

DECLARE @Dest_Col VARCHAR(MAX), @ColToInsert VARCHAR(MAX)

SELECT @Dest_Col = STRING_AGG(Dest_Col, ', ') FROM #TempTable
SELECT @ColToInsert = STRING_AGG(Modified_Col, ', ') FROM #TempTable

如果环境不支持STRING_AGG，可改用XML路径拼接：

SELECT @Dest_Col = STUFF((SELECT ', ' + Dest_Col FROM #TempTable FOR XML PATH('')), 1, 2, '')
SELECT @ColToInsert = STUFF((SELECT ', ' + Modified_Col FROM #TempTable FOR XML PATH('')), 1, 2, '')

步骤3：生成并执行动态SQL #

DECLARE @DySQL VARCHAR(MAX)
SET @DySQL = 'INSERT INTO table_A(' + @Dest_Col + ') SELECT ' + @ColToInsert + ' FROM table_B'

-- 调试时先打印SQL确认语法
PRINT @DySQL
EXEC (@DySQL)

• CONVERT逻辑对齐静态SQL：确保转换语句和手动编写的静态SQL完全一致，避免类型转换失效。
• 明确表关联条件：直接指定源表和目标表，避免模糊匹配导致的字段映射错误。
• 避免循环依赖：用字符串聚合替代WHILE循环，适配Synapse SQL中IDENTITY不连续的特性，同时简化代码。
• 调试优先：执行前用PRINT输出动态SQL，快速验证语法是否正确，排查问题更高效。

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