SQL Server中不同数据类型列的表间快速数据复制方案咨询

针对你这种100多张表、上千列的批量转换场景，手动写转换逻辑完全不现实，给你几个高效的解决方案，按实用性优先级排序：

1. 动态生成带类型转换的INSERT脚本（最推荐） #

利用SQL Server系统视图自动匹配源表（导入的nvarchar表）和目标表（预定义类型表）的列，批量生成带安全转换的INSERT语句，避免手动编写重复代码。

核心思路 #

通过sys.tables、sys.columns、sys.types系统视图获取表和列的元数据，自动生成包含TRY_CONVERT的INSERT语句——这个函数会在转换失败时返回NULL，不会因为个别脏数据中断整个批量操作。

示例脚本 #

DECLARE @SourceSchema NVARCHAR(128) = N'dbo'; -- 源表所在架构
DECLARE @TargetSchema NVARCHAR(128) = N'dbo'; -- 目标表所在架构
DECLARE @SourceTablePrefix NVARCHAR(64) = N'Import_'; -- 替换成你的源表前缀（比如导入的表都叫Import_XXX）

-- 批量生成所有表的转换插入脚本
SELECT 
    N'-- 处理表：' + QUOTENAME(t1.name) + N'
INSERT INTO ' + QUOTENAME(@TargetSchema) + N'.' + QUOTENAME(t2.name) + N' (' + STRING_AGG(QUOTENAME(c2.name), N', ') + N')
SELECT ' + STRING_AGG(
    CASE 
        -- 类型相同直接引用列
        WHEN c1.system_type_id = c2.system_type_id THEN QUOTENAME(c1.name)
        -- 类型不同则用TRY_CONVERT做安全转换，decimal/datetime等类型可按需指定样式
        WHEN typ2.name = N'datetime' THEN N'TRY_CONVERT(datetime, ' + QUOTENAME(c1.name) + N', 120)'
        WHEN typ2.name LIKE N'decimal%' THEN N'TRY_CONVERT(' + QUOTENAME(typ2.name) + N', ' + QUOTENAME(c1.name) + N')'
        ELSE N'TRY_CONVERT(' + QUOTENAME(typ2.name) + N', ' + QUOTENAME(c1.name) + N')'
    END, N', '
) + N'
FROM ' + QUOTENAME(@SourceSchema) + N'.' + QUOTENAME(t1.name) + N';' AS InsertScript
FROM sys.tables t1
JOIN sys.columns c1 ON t1.object_id = c1.object_id
-- 假设源表和目标表同名，不同名的话需要调整匹配逻辑（比如用后缀区分）
JOIN sys.tables t2 ON REPLACE(t1.name, @SourceTablePrefix, N'') = t2.name
JOIN sys.columns c2 ON t2.object_id = c2.object_id AND c1.name = c2.name
JOIN sys.types typ1 ON c1.system_type_id = typ1.system_type_id
JOIN sys.types typ2 ON c2.system_type_id = typ2.system_type_id
WHERE t1.name LIKE @SourceTablePrefix + N'%'
GROUP BY t1.name, t2.name;

进阶：捕获转换失败的行 #

如果需要定位脏数据，可以扩展脚本，把转换失败的行插入到专门的错误表：

-- 示例：为单个表生成带错误捕获的脚本
DECLARE @SourceTable NVARCHAR(128) = N'Import_Orders';
DECLARE @TargetTable NVARCHAR(128) = N'Orders';

-- 自动创建错误表
IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = N'Error_' + @SourceTable)
BEGIN
    SELECT * INTO N'Error_' + @SourceTable FROM @TargetTable WHERE 1=0;
    ALTER TABLE N'Error_' + @SourceTable ADD ErrorMessage NVARCHAR(MAX), FailedColumn NVARCHAR(128);
END

-- 插入转换成功的行
INSERT INTO @TargetTable
SELECT TRY_CONVERT(datetime, OrderDate), TRY_CONVERT(decimal(18,2), Amount), CustomerID
FROM @SourceTable
WHERE TRY_CONVERT(datetime, OrderDate) IS NOT NULL AND TRY_CONVERT(decimal(18,2), Amount) IS NOT NULL;

-- 插入转换失败的行并记录错误信息
INSERT INTO N'Error_' + @SourceTable
SELECT OrderDate, Amount, CustomerID,
       N'转换失败：OrderDate=' + ISNULL(OrderDate, N'NULL') + N' 或 Amount=' + ISNULL(Amount, N'NULL'),
       CASE WHEN TRY_CONVERT(datetime, OrderDate) IS NULL THEN N'OrderDate' ELSE N'Amount' END
FROM @SourceTable
WHERE TRY_CONVERT(datetime, OrderDate) IS NULL OR TRY_CONVERT(decimal(18,2), Amount) IS NULL;

2. 用SSIS批量迁移（适合可视化操作） #

如果你熟悉SQL Server Integration Services（SSIS），可以通过可视化工具完成批量转换，还能直观处理错误数据：

• 步骤1：打开SQL Server Data Tools（SSDT），新建Integration Services项目。
• 步骤2：拖入Foreach循环容器，配置循环遍历所有源表（用SQL查询SELECT name FROM sys.tables WHERE name LIKE 'Import_%'获取表列表，存入变量）。
• 步骤3：在循环容器内添加数据流任务，进入数据流面板：
  • 添加OLE DB源，用变量拼接查询语句SELECT * FROM ?，映射表名变量。
  • 添加数据转换组件，将每个nvarchar列转换为目标表对应的类型（比如nvarchar→datetime、decimal）。
  • 添加OLE DB目标，连接到预定义表，自动匹配列映射。
• 步骤4：配置错误输出：在数据转换组件的错误输出中选择「重定向行」，添加另一个OLE DB目标（错误表），把转换失败的行导进去。
• 步骤5：运行包，即可批量完成所有表的转换插入。

3. 重新导入原始CSV（如果文件还在） #

如果你的原始CSV文件还保留着，直接重新导入到预定义表会更高效，跳过中间的nvarchar转换步骤：

• 在SSMS中右键数据库→任务→导入数据，选择「平面文件源」加载CSV。
• 目标选择你的SQL Server数据库，在「选择源表和视图」步骤，将CSV映射到对应的预定义表。
• 编辑列映射，确认SSIS自动匹配的类型转换逻辑，勾选「保存SSIS包」方便后续重复执行。
• 执行导入，直接将CSV数据按正确类型写入预定义表。

关键注意事项 #

• 始终用TRY_CONVERT/TRY_CAST替代CONVERT/CAST，避免个别脏数据中断整个批量操作。
• 对于decimal类型，要确认目标表的精度/小数位数能容纳源数据，避免转换溢出。
• 对于datetime类型，若源数据格式特殊，可在TRY_CONVERT中指定样式参数（比如TRY_CONVERT(datetime, Col1, 103)对应dd/mm/yyyy格式）。

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