declare @sql nvarchar(2000),@sql1 nvarchar(2000),@sql2 nvarchar(2000)
declare @a varchar(20),@b varchar(20),@c varchar(20),@e varchar(20)
--建表
drop table temptable
declare t1 cursor for
select code from table1
open t1
fetch next from t1 into @a
set @sql='create table temptable ('
while @@FETCH_STATUS<>-1
begin
set @sql=@sql+ltrim(@a)+' varchar(20),'
fetch next from t1 into @a
end
set @sql=left(@sql,len(@sql)-1)+')'
--print @sql
exec(@Sql)
close t1
deallocate t1
--写记录
declare t2 cursor for
select b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='U' and a.name='table1' and b.name<>'code'
open t2
fetch next from t2 into @b
while @@FETCH_STATUS<>-1
begin
set @sql1='insert into temptable('
set @sql2=' values('
declare t3 cursor for
select b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='U' and a.name='temptable'
open t3
fetch next from t3 into @c
while @@FETCH_STATUS<>-1
begin
set @sql='select @d='+@b+' from table1 where code='''+@c+''''
exec sp_executesql @sql,N'@d varchar(20) output',@d=@e output
set @sql1=@sql1+rtrim(@c)+','
set @sql2=@sql2+''''+rtrim(@e)+''','
fetch next from t3 into @c
end
close t3
deallocate t3
-- print @sql1
-- print @sql2
set @sql=left(@sql1,len(@sql1)-1)+')'+left(@sql2,len(@sql2)-1)+')'
-- print @sql
exec (@sql)
fetch next from t2 into @b
end
close t2
deallocate t2
go
select * from temptable
