-- ======================================================
--根据表中数据生成insert语句的存储过程
--建立存储过程,执行spGenInsertSQL 表名
--感谢playyuer
----感谢szyicol
-- ======================================================
- 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)
- 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
-
- exec (@sql)
- end
- GO
-- ======================================================
--根据表中数据生成insert语句的存储过程
--建立存储过程,执行proc_insert 表名
--感谢Sky_blue
--感谢szyicol
-- ======================================================
- 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 '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
(秩名) |