sqlserver 生成insert 存储过程

--简单insert语句
create     proc   spGenInsertSQL  
  @TableName   as   varchar(100)  
  as  
  --declare   @TableName   varchar(100)  
  --set   @TableName   =   'orders'  
  --set   @TableName   =   'eeducation'  
  DECLARE   xCursor   CURSOR   FOR  
  SELECT   name,xusertype  
  FROM   syscolumns  
  WHERE   (id   =   OBJECT_ID(@TableName))  
  declare   @F1   varchar(100)  
  declare   @F2   integer  
  declare   @SQL   varchar(8000)  
  set   @sql   ='SELECT   ''INSERT   INTO   '   +   @TableName   +   '   VALUES('''  
  OPEN   xCursor  
  FETCH   xCursor   into   @F1,@F2  
  WHILE   @@FETCH_STATUS   =   0  
  BEGIN  
          set   @sql   =@sql   +  
                              +   case   when   @F2   IN   (35,58,99,167,175,231,239,61)   then   '   +   case   when   '   +   @F1   +   '   IS   NULL   then   ''''   else   ''''''''   end   +   '     else   '+'   end  
                              +   'replace(ISNULL(cast('   +   @F1   +   '   as   varchar(8000)),''NULL''),'''''''','''''''''''')'    
                              +   case   when   @F2   IN   (35,58,99,167,175,231,239,61)   then   '   +   case   when   '   +   @F1   +   '   IS   NULL   then   ''''   else   ''''''''   end   +   '     else   '+'   end  
                              +   char(13)   +   ''','''    
          FETCH   NEXT   FROM   xCursor   into   @F1,@F2  
  END  
  CLOSE   xCursor  
  DEALLOCATE   xCursor  
  set   @sql   =   left(@sql,len(@sql)   -   5)   +   '   +   '')''   FROM   '   +   @TableName  
  exec   (@sql)  
   
  go
EXEC spGenInsertSQL basorgteam
--完整insert语句（多列）
CREATE PROCEDURE dbo.OutputData

@tablename sysname

AS

declare @column varchar(1000)

declare @columndata varchar(1000)

declare @sql varchar(4000)

declare @xtype tinyint

declare @name sysname

declare @objectId int

declare @objectname sysname

declare @ident int


set nocount on

set @objectId=object_id(@tablename)

if @objectId is null -- 判断对象是否存在

begin

print @tablename + '对象不存在'

return

end

set @objectname=rtrim(object_name(@objectId))

if @objectname is null or charindex(@objectname,@tablename)=0

begin

print @tablename + '对象不在当前数据库中'

return

end

if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判断对象是否是表

begin

print @tablename + '对象不是表'

return

end

select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80

if @ident is not null

print 'SET IDENTITY_INSERT '+ @TableName + ' ON'

--定义游标，循环取数据并生成Insert语句

declare syscolumns_cursor cursor for

select c.name,c.xtype from syscolumns c

where c.id=@objectid

order by c.colid

--打开游标

open syscolumns_cursor

set @column=''

set @columndata=''

fetch next from syscolumns_cursor into @name,@xtype

while @@fetch_status <> -1

begin

if @@fetch_status <> -2

begin

if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理

begin

set @column=@column +

case when len(@column)=0 then ''

else ','

end + @name

set @columndata = @columndata +

case when len(@columndata)=0 then ''

else ','','','

end +

case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char

when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar

when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime

when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime

when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier

else @name

end

end

end

fetch next from syscolumns_cursor into @name,@xtype

end

close syscolumns_cursor

deallocate syscolumns_cursor

set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename

print '--'+@sql

exec(@sql)

if @ident is not null

print 'SET IDENTITY_INSERT '+@TableName+' OFF'

--执行存储过程语句
exec OutputData functionButtonInfo

--完整insert语句（单列，每列的长度最大为256，如果insert语句超过256长度的话，就不要用这个）
create proc proc_insert (@tablename varchar(256))

as

begin

set nocount on

declare @sqlstr nvarchar(10000)

declare @sqlstr1 nvarchar(10000)

declare @sqlstr2 nvarchar(10000)

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 =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

-- print @sqlstr

exec( @sqlstr)

set nocount off

end

go


exec proc_insert functionButtonInfo