首页 / 数据库 / SQLServer / 将表数据生成Insert脚本 比较好用的生成插入语句的SQL脚本

复制代码 代码如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: 华岭
-- Create date: 2008-10-28
-- Description: 将表数据生成Insert脚本
-- Demo : exec pCreateInsertScript "BexmCodeType","dictypeid = 61"
-- =============================================
alter proc [dbo].pCreateInsertScript （@tablename varchar（256）,@con nvarchar（400））
as
begin
set nocount on
declare @sqlstr varchar（4000）
declare @sqlstr1 varchar（4000）
declare @sqlstr2 varchar（4000）
select @sqlstr="select ""insert "+@tablename
select @sqlstr1=""
select @sqlstr2="（"
select @sqlstr1="values （""+"
select @sqlstr1=@sqlstr1+col+"+"",""+" ,@sqlstr2=@sqlstr2+name +"," from （select case
when a.xtype =173 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（"+convert（varchar（4）,a.length*2+2）+"）,"+a.name +"）"+" end"
when a.xtype =104 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（1）,"+a.name +"）"+" end"
when a.xtype =175 then "case when "+a.name+" is null then ""NULL"" else "+"""""""""+"+"replace（"+a.name+","""""""",""""""""""""）" + "+"""""""""+" end"
when a.xtype =61 then "case when "+a.name+" is null then ""NULL"" else "+"""""""""+"+"convert（varchar（23）,"+a.name +",121）"+ "+"""""""""+" end"
when a.xtype =106 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（"+convert（varchar（4）,a.xprec+2）+"）,"+a.name +"）"+" end"
when a.xtype =62 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（23）,"+a.name +",2）"+" end"
when a.xtype =56 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（11）,"+a.name +"）"+" end"
when a.xtype =60 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（22）,"+a.name +"）"+" end"
when a.xtype =239 then "case when "+a.name+" is null then ""NULL"" else "+"""""""""+"+"replace（"+a.name+","""""""",""""""""""""）" + "+"""""""""+" end"
when a.xtype =108 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（"+convert（varchar（4）,a.xprec+2）+"）,"+a.name +"）"+" end"
when a.xtype =231 then "case when "+a.name+" is null then ""NULL"" else "+"""""""""+"+"replace（"+a.name+","""""""",""""""""""""）" + "+"""""""""+" end"
when a.xtype =59 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（23）,"+a.name +",2）"+" end"
when a.xtype =58 then "case when "+a.name+" is null then ""NULL"" else "+"""""""""+"+"convert（varchar（23）,"+a.name +",121）"+ "+"""""""""+" end"
when a.xtype =52 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（12）,"+a.name +"）"+" end"
when a.xtype =122 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（22）,"+a.name +"）"+" end"
when a.xtype =127 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（6）,"+a.name +"）"+" end"
when a.xtype =48 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（6）,"+a.name +"）"+" end"
when a.xtype =165 then "case when "+a.name+" is null then ""NULL"" else "+"convert（varchar（"+convert（varchar（4）,a.length*2+2）+"）,"+a.name +"）"+" end"
when a.xtype =167 then "case when "+a.name+" is null then ""NULL"" else "+"""""""""+"+"replace（"+a.name+","""""""",""""""""""""）" + "+"""""""""+" end"
else """NULL"""
end as col,a.colid,a.name
from syscolumns a where a.id = object_id（@tablename）
and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
）t order by colid
select @sqlstr=@sqlstr+left（@sqlstr2,len（@sqlstr2）-1）+"） "+left（@sqlstr1,len（@sqlstr1）-3）+"）"" from "+@tablename + " where 1=1 and " + isnull（@con,""）
print @sqlstr
exec（ @sqlstr）
set nocount off
end