根据基本表结构及其数据生成 INSERT .._sqlserver

create  proc spGenInsertSQL
@TableName as varchar(100)
as
–declare @TableName varchar(100)
–set @TableName = ‘orders’

http://www.gaodaima.com/35612.html根据基本表结构及其数据生成 INSERT .._sqlserver

–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),”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 ‘+@tablename
  –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 ‘+@tablename
  print @sql
  exec (@sql)
/*
select *
from syscolumns 
where id = object_id(‘test’) and autoval is null
*/
end

欢迎大家阅读《根据基本表结构及其数据生成 INSERT .._sqlserver》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)