SQL Server跨无链路环境生成全量/筛选插入脚本实现数据迁移问询

以下所有方案均可以直接在SQL Server Management Studio的查询窗口执行，生成的结果就是可直接在生产环境运行的SQL脚本，无需额外工具依赖，完全适配生产环境无外部数据链路的要求。

1.1 全量导出表数据 #

替换代码中的你的表名后执行，输出结果的每一行都是对应表一条记录的INSERT语句：

DECLARE @TableName NVARCHAR(128) = N'你的表名'
DECLARE @SQL NVARCHAR(MAX) = N''

SELECT @SQL = @SQL + N'INSERT INTO ' + QUOTENAME(@TableName) + N' (' + STUFF((
    SELECT N', ' + QUOTENAME(name) 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(@TableName)
    AND is_identity = 0 -- 若需要保留自增列赋值，删除此行即可
    ORDER BY column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, N'') + N') VALUES (' + STUFF((
    SELECT N', N''' + REPLACE(CAST(' + QUOTENAME(name) + ' AS NVARCHAR(MAX)), '''', '''''') + ''''
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(@TableName)
    AND is_identity = 0 -- 若需要保留自增列赋值，删除此行即可
    ORDER BY column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, N'') + N');' + CHAR(13)
FROM sys.tables WHERE name = @TableName

SET @SQL = N'SELECT ' + QUOTENAME(@SQL, '''') + N' FROM ' + QUOTENAME(@TableName)
EXEC sp_executesql @SQL

1.2 按筛选条件导出（支持先删后插场景） #

适配仅操作指定分类记录、不触碰用户数据的需求，替换你的表名和筛选条件后执行，输出结果开头为指定条件的DELETE语句，后面紧跟所有符合条件记录的INSERT语句，可直接在生产环境执行：

DECLARE @TableName NVARCHAR(128) = N'你的表名'
-- 替换为实际筛选条件，示例：Category = ''System'' AND Collection_Name = ''Status''
DECLARE @FilterCondition NVARCHAR(MAX) = N'你的筛选条件'
DECLARE @SQL NVARCHAR(MAX) = N''
DECLARE @DeleteSQL NVARCHAR(MAX) = N'DELETE FROM ' + QUOTENAME(@TableName) + N' WHERE ' + @FilterCondition + N';' + CHAR(13) + CHAR(13)

-- 生成插入语句模板
SELECT @SQL = @SQL + N'INSERT INTO ' + QUOTENAME(@TableName) + N' (' + STUFF((
    SELECT N', ' + QUOTENAME(name) 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(@TableName)
    AND is_identity = 0
    ORDER BY column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, N'') + N') VALUES (' + STUFF((
    SELECT N', N''' + REPLACE(CAST(' + QUOTENAME(name) + ' AS NVARCHAR(MAX)), '''', '''''') + ''''
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(@TableName)
    AND is_identity = 0
    ORDER BY column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, N'') + N');' + CHAR(13)
FROM sys.tables WHERE name = @TableName

-- 拼接筛选条件生成最终语句
SET @SQL = N'SELECT N''' + @DeleteSQL + N''' + (SELECT ' + QUOTENAME(@SQL, '''') + N' FROM ' + QUOTENAME(@TableName) + N' WHERE ' + @FilterCondition + N' FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)'')'
EXEC sp_executesql @SQL

若不需要先删除原有记录，仅导出筛选后的INSERT语句，将@DeleteSQL变量赋值为空字符串即可。

针对数十张表频繁更新的需求，可通过临时配置表批量生成所有表的更新脚本，仅需修改配置部分即可一键生成全量更新脚本：

-- 创建临时配置表，用完自动销毁，也可改成永久表留作后续使用
CREATE TABLE #ExportConfig (
    TableName NVARCHAR(128),
    FilterCondition NVARCHAR(MAX), -- 全量导出填NULL即可
    NeedDelete BIT -- 1=导出前先删除符合条件的记录，0=仅导出INSERT语句
)

-- ****************** 仅修改此处配置即可 ******************
INSERT INTO #ExportConfig VALUES
(N'全量导出表1', NULL, 0),
(N'全量导出表2', NULL, 0),
(N'管控配置表', N'Category = ''System'' AND Collection_Name = ''Status''', 1)
-- ********************************************************

-- 批量生成所有脚本
DECLARE @FinalScript NVARCHAR(MAX) = N''
DECLARE @TableName NVARCHAR(128), @Filter NVARCHAR(MAX), @NeedDel BIT
DECLARE cur CURSOR FOR SELECT TableName, FilterCondition, NeedDelete FROM #ExportConfig
OPEN cur
FETCH NEXT FROM cur INTO @TableName, @Filter, @NeedDel

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = N''
    DECLARE @DelSQL NVARCHAR(MAX) = N''
    IF @NeedDel = 1 AND @Filter IS NOT NULL
    BEGIN
        SET @DelSQL = N'DELETE FROM ' + QUOTENAME(@TableName) + N' WHERE ' + @Filter + N';' + CHAR(13) + CHAR(13)
    END

    SELECT @SQL = @SQL + N'INSERT INTO ' + QUOTENAME(@TableName) + N' (' + STUFF((
        SELECT N', ' + QUOTENAME(name) 
        FROM sys.columns 
        WHERE object_id = OBJECT_ID(@TableName)
        AND is_identity = 0
        ORDER BY column_id
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, N'') + N') VALUES (' + STUFF((
        SELECT N', N''' + REPLACE(CAST(' + QUOTENAME(name) + ' AS NVARCHAR(MAX)), '''', '''''') + ''''
        FROM sys.columns 
        WHERE object_id = OBJECT_ID(@TableName)
        AND is_identity = 0
        ORDER BY column_id
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, N'') + N');' + CHAR(13)
    FROM sys.tables WHERE name = @TableName

    DECLARE @TableScript NVARCHAR(MAX)
    SET @SQL = N'SELECT @Out = N''' + @DelSQL + N''' + (SELECT ' + QUOTENAME(@SQL, '''') + N' FROM ' + QUOTENAME(@TableName) + ISNULL(N' WHERE ' + @Filter, N'') + N' FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)'')'
    EXEC sp_executesql @SQL, N'@Out NVARCHAR(MAX) OUT', @Out = @TableScript OUT

    SET @FinalScript += N'-- 处理表：' + @TableName + CHAR(13) + @TableScript + CHAR(13) + N'GO' + CHAR(13) + CHAR(13)
    FETCH NEXT FROM cur INTO @TableName, @Filter, @NeedDel
END

CLOSE cur
DEALLOCATE cur

-- 输出生成的完整更新脚本
SELECT @FinalScript AS FullExportScript

如果表包含二进制、XML等特殊类型字段，可针对性调整字段拼接部分的转换逻辑，默认逻辑适配字符串、数字、日期等绝大多数常见数据类型。

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