SQL Server自动生成INSERT语句代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL Server自动生成INSERT语句
作者:Dezai.CN / 发布于2011/7/26/ 714
<div>
<p class="cnt"><font face="Verdana">--这是一个可以将表中的数据自动生成INSERT语句的存储过程
--有个BUG，如果是自动增长列，数据也会被插入
--在查询分析器中执行完成后，可以从执行结果中自制出SQL脚本。
IF OBJECT_ID('spGenInsertSQL','P') IS NOT NULL 
DROP PROC spGenInsertSQL
GO
CREATE proc 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) </font>
<p class="cnt"><font face="Verdana">then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'</font>
<p class="cnt"><font face="Verdana">when xtype in (58,61)</font>
<p class="cnt"><font face="Verdana">then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'</font>
<p class="cnt"><font face="Verdana">when xtype in (167)</font>
<p class="cnt"><font face="Verdana">then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'</font>
<p class="cnt"><font face="Verdana">when xtype in (231)</font>
<p class="cnt"><font face="Verdana">then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'</font>
<p class="cnt"><font face="Verdana">when xtype in (175)</font>
<p class="cnt"><font face="Verdana">then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'</font>
<p class="cnt"><font face="Verdana">when xtype in (239)</font>
<p class="cnt"><font face="Verdana">then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'</font>
<p class="cnt"><font face="Verdana">else '''NULL'''</font>
<p class="cnt"><font face="Verdana">end as Cols,name</font>
<p class="cnt"><font face="Verdana">from syscolumns </font>
<p class="cnt"><font face="Verdana">where id = object_id(@tablename)</font>
<p class="cnt"><font face="Verdana">) T
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
print @sql
exec (@sql)
end
GO</font>
<p class="cnt">
<p class="cnt">执行如下语句测试结果：
<div class="cnt"><font face="Verdana">exec spGenInsertSQL test

From: <a href="http://www.cnblogs.com/shunyao8210/archive/2008/07/23/1249289.html" target="_blank"><font color="#5d5d5d">http://www.cnblogs.com/shunyao8210/archive/2008/07/23/1249289.html</font></a>

PS:自增列需要调整一下 </font></div>
<div class="cnt"><font face="Verdana">参考 SET IDENTITY_INSERT 表名 ON 

SET IDENTITY_INSERT 表名 OFF 
</font></div>
</div>
试试其它关键字
　自动生成INSERT语句　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)