SQL如何按相同数据类型匹配两表列完成跨表数据插入

不需要使用循环删列的复杂方案，通过系统视图读取两张表的列元数据，按数据类型做有序配对后拼接动态SQL执行即可，逻辑稳定易维护。

核心实现代码（适配SQL Server，和示例语法一致） #

-- 声明变量存储最终拼接的插入SQL
DECLARE @insertSQL NVARCHAR(MAX)

;WITH TargetCols AS (
    -- 读取目标表test1的列元数据，同类型列按表中定义顺序排序编号
    SELECT 
        c.name AS ColName,
        t.name AS DataType,
        ROW_NUMBER() OVER (PARTITION BY t.name ORDER BY c.column_id) AS TypeOrder
    FROM sys.columns c
    JOIN sys.types t ON c.user_type_id = t.user_type_id
    WHERE c.object_id = OBJECT_ID('test1')
),
SourceCols AS (
    -- 读取源表test2的列元数据，同类型列按表中定义顺序排序编号
    SELECT 
        c.name AS ColName,
        t.name AS DataType,
        ROW_NUMBER() OVER (PARTITION BY t.name ORDER BY c.column_id) AS TypeOrder
    FROM sys.columns c
    JOIN sys.types t ON c.user_type_id = t.user_type_id
    WHERE c.object_id = OBJECT_ID('test2')
)
-- 按类型+同类型序号配对，拼接生成最终INSERT语句
SELECT @insertSQL = N'
INSERT INTO test1 (' + STRING_AGG(tc.ColName, N', ') + N')
SELECT ' + STRING_AGG(sc.ColName, N', ') + N'
FROM test2'
FROM TargetCols tc
JOIN SourceCols sc 
    ON tc.DataType = sc.DataType 
    AND tc.TypeOrder = sc.TypeOrder

-- 先打印生成的SQL校验映射逻辑
PRINT @insertSQL
-- 校验无误后放开注释执行
-- EXEC sp_executesql @insertSQL

匹配规则说明 #

• 两张表的列会先按数据类型分组，同类型下的列按照表内定义的先后顺序编号，相同编号的同类型列自动配对
• 以你给出的表结构为例，最终生成的INSERT语句如下，完全符合按类型匹配的需求：

INSERT INTO test1 (columnone, columntwo, columnthree, columnfour)
SELECT column2, column1, column4, column3
FROM test2

• 对应匹配关系：test2的varchar(max)列column2匹配test1的varchar(max)列columnone，test2的text列column1匹配test1的text列columntwo，test2的datetime列column4匹配test1的datetime列columnthree，test2的int列column3匹配test1的int列columnfour。

注意事项 #

• 原示例中的插入语句存在两处问题：
  • 日期值没有加单引号，7/4/24会被SQL Server识别为整数除法运算，得到数值结果而非日期，需要写成带单引号的日期字符串，推荐用'YYYY-MM-DD'的ISO格式避免地域格式歧义
  • 日期6/31/22是非法值，6月只有30天，需要修正为合法日期
    修正后的插入语句参考：

INSERT INTO test2 (column1, column2, column3, column4)
VALUES ('foo', 'suit3333', 7, '2024-07-04'), ('bar', 'person24', 9, '2022-06-30')

• 代码默认先通过PRINT输出拼接好的SQL语句，确认列映射符合预期后再放开执行注释，避免误写数据。
• 如果两张表同数据类型的列数量不一致，代码会自动按数量更少的一侧完成配对，不会抛出错误；如果需要强校验，可以增加判断逻辑，当各类型列数不匹配时提前抛出提示。
• SQL Server 2017以下版本不支持STRING_AGG函数，可以把列名拼接部分替换为FOR XML PATH写法实现，核心配对逻辑不变。

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