搜索:
首页 >> 数据库 >> MsSQL教程 >> 根据表中数据生成insert语句的存储过程

根据表中数据生成insert语句的存储过程

2008-11-10 asp之家 投递文章

昨时要导一些数据,从网上搜到的。字段多时insert 语句生成的不完整了,还没有找到原因..

有个缺点……就是标识种子的列 也insert了

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
--print @sql
exec (@sql)
end
go
Tags:insert  存储过程  数据 
相关文章
手机版 MsSQL教程 Asp之家 Aspxhome.com
闽ICP备06017341号