输入表名，生成插入语句

使用场景：误删数据，需要使用之前备份数据插入到生产环境的数据库，不覆盖历史数据


以下是SQL存储过程，插入到数据库中后即可使用。

1. drop proc proc_insert
   
2. go
   
3. create proc proc_insert (@tablename varchar(256))
   
4. as
   
5. begin
   
6. set nocount on
   
7. declare @sqlstr varchar(4000)
   
8. declare @sqlstr1 varchar(4000)
   
9. declare @sqlstr2 varchar(4000)
   
10. select @sqlstr='select ''insert '+@tablename
    
11. select @sqlstr1=''
    
12. select @sqlstr2=' ('
    
13. select @sqlstr1= ' values ( ''+'
    
14. select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
    
15. -- 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'
    
16. when a.xtype =127 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(20),'+a.name +')'+' end'
    
17. when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
    
18. when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    
19. when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
    
20. 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'
    
21. when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
    
22. when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
    
23. when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
    
24. when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    
25. 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'
    
26. when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    
27. when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
    
28. when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
    
29. when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
    
30. when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
    
31. when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
    
32. -- 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'
    
33. when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    
34. else '''NULL'''
    
35. end as col,a.colid,a.name
    
36. 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
    
37. )t order by colid
    
38. 
    
39. select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
    
40. -- print @sqlstr
    
41. exec( @sqlstr)
    
42. set nocount off
    
43. end
    
44. go
    
45. 
    
46. select * from systypes

复制代码

使用方法：
exec proc_insert 你的表名
如：exec proc_insert T_10

输入表名，生成插入语句

使用场景：误删数据，需要使用之前备份数据插入到生产环境的数据库，不覆盖历史数据


以下是SQL存储过程，插入到数据库中后即可使用。

1. drop proc proc_insert
   
2. go
   
3. create proc proc_insert (@tablename varchar(256))
   
4. as
   
5. begin
   
6. set nocount on
   
7. declare @sqlstr varchar(4000)
   
8. declare @sqlstr1 varchar(4000)
   
9. declare @sqlstr2 varchar(4000)
   
10. select @sqlstr='select ''insert '+@tablename
    
11. select @sqlstr1=''
    
12. select @sqlstr2=' ('
    
13. select @sqlstr1= ' values ( ''+'
    
14. select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
    
15. -- 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'
    
16. when a.xtype =127 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(20),'+a.name +')'+' end'
    
17. when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
    
18. when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    
19. when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
    
20. 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'
    
21. when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
    
22. when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
    
23. when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
    
24. when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    
25. 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'
    
26. when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    
27. when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
    
28. when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
    
29. when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
    
30. when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
    
31. when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
    
32. -- 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'
    
33. when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    
34. else '''NULL'''
    
35. end as col,a.colid,a.name
    
36. 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
    
37. )t order by colid
    
38. 
    
39. select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
    
40. -- print @sqlstr
    
41. exec( @sqlstr)
    
42. set nocount off
    
43. end
    
44. go
    
45. 
    
46. select * from systypes

复制代码

使用方法：
exec proc_insert 你的表名
如：exec proc_insert T_10