IDC资讯中心
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 =@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
