根据基本表结构及其数据生成 INSERT ... 的 SQL 语句

Question

CREATE      PROCEDURE  dbo.UspOutputData  
           @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  'The  object  not  exists'    
           return    
end    
set    @objectname=rtrim(object_name(@objectId))  
 
if    @objectname  is  null  or  charindex(@objectname,@tablename)=0  --此判断不严密  
begin  
           print  'object  not  in  current  database'  
           return  
end  
 
if  OBJECTPROPERTY(@objectId,'IsTable')    <  >  1  --  判斷對象是否是table    
begin    
           print  'The  object  is  not  table'    
           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'  
 
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'  
 
GO

Answer 1

create proc proc_insert (@tablename varchar(256))
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 'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'
when a.xtype =104 then 'convert(varchar(1),'+a.name +')'
when a.xtype =175 then '''''''''+'+a.name + '+'''''''''
when a.xtype =61 then '''''''''+'+'convert(varchar(23),'+a.name +',120)'+ '+'''''''''
when a.xtype =106 then 'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'
when a.xtype =62 then 'convert(varchar(23),'+a.name +',2)'
when a.xtype =56 then 'convert(varchar(11),'+a.name +')'
when a.xtype =60 then 'convert(varchar(22),'+a.name +')'
when a.xtype =239 then '''''''''+'+a.name + '+'''''''''
when a.xtype =108 then 'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'
when a.xtype =231 then '''''''''+'+a.name + '+'''''''''
when a.xtype =59 then 'convert(varchar(23),'+a.name +',2)'
when a.xtype =58 then '''''''''+'+'convert(varchar(23),'+a.name +',120)'+ '+'''''''''
when a.xtype =52 then 'convert(varchar(12),'+a.name +')'
when a.xtype =122 then 'convert(varchar(22),'+a.name +')'
when a.xtype =48 then 'convert(varchar(6),'+a.name +')'
when a.xtype =165 then 'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'
when a.xtype =167 then '''''''''+'+a.name + '+'''''''''
else a.name
end as col,a.colid,a.name
from syscolumns a where a.id = object_id(@tablename)  
)t order by colid

select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
exec( @sqlstr)
set nocount off
end
go

Answer 2

没看到问题，先看到了四颗闪闪的红星PFPF

Answer 3

执行：spGenInsertSQL 'dbo.Purview_Object'
结果：INSERT INTO dbo.Purview_Object VALUES('asd','234','','Y','01 15 2003  3:56PM','01 15 2003  3:56PM',
实际：asd，234，，Y，2003-01-15 15:56:01.387，2003-01-15 15:56:01.387，0x000000000000025D
说明：最后一个为timestamp类型。执行：proc_insert 'dbo.Purview_Object'
结果：
服务器: 消息 260，级别 16，状态 1，行 1
不允许从数据类型 nvarchar 到数据类型 timestamp 的隐性转换（表 'Role.dbo.Purview_Object'，列 'adasd'）。请使用 CONVERT 函数来运行此查询。
服务器: 消息 403，级别 16，状态 1，行 1
对数据类型而言运算符无效。运算符为 add，类型为 timestamp。执行：UspOutputData 'Purview_Object'
结果：insert  Purview_Object(ObjectID,ObjectName,ObjectType,Inherit,CreateDate,ModifyDate)  values( N'asd' , N'234' , '' , 'Y' , '2003-01-15 15:56:01.387' , '2003-01-15 15:56:01.387' )

Answer 4

小干部儿，辛苦了！谢谢。我还真不知道这样写INSERT语句到底在那用。

Answer 5

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),''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) + ''',''' 
看不懂
解释一下
谢谢！

Answer 6

把我的连接也贴过来
http://expert.csdn.net/Expert/topic/1374/1374946.xml

Answer 7

哈哈。J老师。在生成sql脚本的时候，sqlserver不能生成数据的脚本！！

Answer 8

何必呢，使用rapid sql 能够轻松的完成，留点时间好好研究学习新的东西吧

Answer 9

如果遇到BLOB型的数据又该怎么办呢，比如Image Video等？

Answer 10

前些天刚在DELPHI问过这个问题，没人能解答，看看这里的高手能不能解决？

Answer 11

where can i download rapid sql?

Answer 12

set @sql =@sql +
              + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case 
@sql后面怎么又两个+号呢？有点不明白

Answer 13

又改了改,支持 char(n) nchar(n)
参考了: sky_bluealter 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)                                
                       then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
                  when xtype in (58,61)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
          when xtype in (167)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                  when xtype in (231)
                       then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                  when xtype in (175)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'
                  when xtype in (239)
                       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