根据基本表结构及其数据生成 ins&#101;rt ... 的 SQL

2024-07-21 02:09:10

字体：大中小

来源：转载

供稿：网友

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 [email protected] +
              + 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) + ''','''
    fetch next from xcursor into @f1,@f2
end
close xcursor
deallocate xcursor
set @sql = left(@sql,len(@sql) - 5) + ' + '')'' from ' + @tablename
print @sql
exec (@sql)

第二版:2003.03.08

alter 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 '[email protected]
--print @sql
exec (@sql)
end

第三版: 2003.3.9

alter   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,175)
                       then 'case when '+ name +' is null then ''null'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+''''''''' + ' end'
                  when xtype in (231,239)
                       then 'case when '+ name +' is null then ''null'' else '+'''n'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+''''''''' + ' end'
                  else '''null'''
                end as cols,name
           from syscolumns
          where id = object_id(@tablename) and autoval is null
        ) t
set @sql ='select ''insert into ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlvalues,len(@sqlvalues)-4) + ')'' from '[email protected]
print @sql
exec (@sql)
/*
select *
from syscolumns
where id = object_id('test') and autoval is null
*/
end

上一篇：Stored Procedure（存储过程）编写经验和优化措施

下一篇：Decrypt encrypted stored procedures