• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

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

sqlserver 搞代码 3年前 (2018-06-16) 75次浏览 已收录 0个评论

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 [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

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


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址