select identity(int,1,1) as rowid,* into #t from t1
insert into t2(key1,key2,...,key9)
select
max(case rowid when 1 then id end),
max(case rowid when 2 then id end),
......,
max(case rowid when 9 then id end)
from
#T
drop table #T
insert into t2(key1,key2,...,key9)
select
max(case rowid when 1 then id end),
max(case rowid when 2 then id end),
......,
max(case rowid when 9 then id end)
from
#T
drop table #T
该表中已有继承了标识属性的列 'id
set @s=''
if (select count(*) from t1)<9
print '数据没有9条,不能Insert!'
else
begin
select top 9 @s = @s + ',' + cast([ID] as varchar(20)) from t1
set @s = 'insert into t2(key1,key2,key3,key4,key5,key6,key7,key8,key9) values( ' + stuff(@s,1,1,'') + ' )'
print @s
exec (@s)
end
----------------------------------------------------------------
insert into t2(key1,key2,...,key9)
select
max(case t.rowid when 1 then ID end),
max(case t.rowid when 2 then ID end),
......,
max(case t.rowid when 9 then ID end)
from
(select
a.ID,
count(b.ID) as rowid
from
t1 a,t1 b
where
a.ID>=b.ID
group by
a.ID) t