SQL Server 存储过程生成 Insert SQL 语句

解决思路与原理

该方案的核心在于动态构建 SQL 字符串。存储过程会查询系统的元数据信息，获取指定表的所有字段名称及其数据类型。根据字段类型（`xtype`），它应用不同的逻辑将数据库中的原始值转换为 SQL 字符串中的字面量：

• 数字与二进制：直接转换或设为 NULL。
• 字符类型 (varchar/nchar)：自动包裹单引号，并处理内部包含的单引号以避免 SQL 语法错误。
• 日期类型：转换为标准时间格式字符串。
• GUID (uniqueidentifier)：保持原样或转为字符串。

最终拼接出类似 INSERT INTO [TableName] ([Col1], [Col2]) VALUES ('Val1', 'Val2') 的完整语句。

第1步：准备存储过程代码

以下存储过程名为 [dbo].[spGenInsertSQL]，接收一个参数 @tablename（即需要生成插入语句的表名）。该代码利用了 syscolumns 系统视图来获取列信息。

完整存储过程实现

Create proc [dbo].[spGenInsertSQL] (@tablename varchar(256))
as
begin
    declare @sql varchar(8000)
    declare @sqlValues varchar(8000)
    set @sql =' ('
    set @sqlValues = 'values ('''+'
    select @sqlValues = @sqlValues + cols + ' + ','' + ', @sql = @sql + '[' + name + '],'
    from
        (select case
            when xtype in (48,52,56,59,60,62,104,106,108,122,127) 
                then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
            when xtype in (58,61)
                -- datetime and smalldatetime
                then 'case when '+ name +' is null then ''NULL'' else '+'''''''' + ' + 'cast('+ name +' as varchar)' + '+'''''''''+' end'
            when xtype in (167)
                -- varchar
                then 'case when '+ name +' is null then ''NULL'' else '+'''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
            when xtype in (231)
                -- nvarchar
                then 'case when '+ name +' is null then ''NULL'' else '+'N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
            when xtype in (175)
                -- char
                then 'case when '+ name +' is null then ''NULL'' else '+'''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
            when xtype in (239)
                -- nchar
                then 'case when '+ name +' is null then ''NULL'' else '+'N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
            else '''NULL'''
            end as Cols,name
        from syscolumns 
        where id = object_id(@tablename)
        ) T
    set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
    print @sql
    exec (@sql)
end

第2步：执行存储过程生成语句

在 SQL Server Management Studio (SSMS) 中选中要操作的数据库，执行上述代码完成存储过程的创建。随后，调用该存储过程并传入具体的表名即可生成 SQL 语句。

调用示例

-- 假设有一个名为 SysSample 的测试表
EXEC [dbo].[spGenInsertSQL] 'SysSample'

第3步：获取并验证结果

执行后，输出窗口（Messages）和查询结果集将显示生成的 SQL 语句。您可以将这些语句复制出来，在新的数据库实例或服务器上执行，以完成数据迁移。

生成结果示例

INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('0002CA83-AF2F-4D8F-A345-33CA1CC7CF3C','任务调度系统',18,'2013-01-02 21:42:30.013','',NULL,'2013-01-02 21:42:30.013')
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('0004A6F3-EC28-4D1F-BA40-0FC4B2218C92','任务调度系统',18,'2013-07-09 19:36:00.060','',NULL,'2013-07-09 19:36:00.060')
-- ... 更多行

注意事项与局限性

• 大数据量性能：如果表中记录数量巨大（例如数十万条），生成的 SQL 文件可能会非常大，导致内存溢出或执行超时。对于大规模数据迁移，建议使用 BCP、SQL Server Integration Services (SSIS) 或直接的 SELECT INTO / Linked Server 方式。
• 依赖项：此方法依赖于 syscolumns 视图，虽然目前仍可用，但在未来的 SQL Server 版本中微软推荐转向 sys.columns。若需更高兼容性，可参考官方文档对元数据查询方式进行更新。
• 特殊字符：代码中已对单引号进行了基本的转义处理（replace），但对于极特殊的二进制数据或非标准字符集，可能需要额外校验生成的 SQL 语句。

以上就是 SQL Server 存储过程生成 Insert SQL 语句的详细内容，更多关于 SQL Server 数据迁移技巧的资料请关注本站其它相关文章！