SQL Server 2005导出表中数据的SQL脚本形式（即INSERT语句）

最新推荐文章于 2021-02-02 01:55:09 发布

转载最新推荐文章于 2021-02-02 01:55:09 发布 · 180 阅读

本文介绍了一种使用SQL脚本自动插入数据的方法，通过自定义过程简化了数据库操作，特别针对特定表的字段进行智能处理，确保数据的正确性和一致性。适用于需要频繁或自动化数据输入的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 1 CREATE   proc spGenInsertSQL (@tablename varchar(256))
 2 as
 3 begin
 4 declare @sql varchar(8000)
 5 declare @sqlValues varchar(8000)
 6 set @sql =' ('
 7 set @sqlValues = 'values (''+'
 8 select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
 9 from
10       (select case
11                 when xtype in (48,52,56,59,60,62,104,106,108,122,127)       
12 
13                      then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
14 
15                 when xtype in (58,61)
16 
17                      then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
18 
19                when xtype in (167)
20 
21                      then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
22 
23                 when xtype in (231)
24 
25                      then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
26 
27                 when xtype in (175)
28 
29                      then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
30 
31                 when xtype in (239)
32 
33                      then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
34 
35                 else '''NULL'''
36 
37               end as Cols,name
38 
39          from syscolumns 
40 
41         where id = object_id(@tablename)
42 
43       ) T
44 set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
45 print @sql
46 exec (@sql)
47 end
48 GO