–建立主表临时表
create table #temp
(
rownumber bigint,
orderseqno varchar(36),
goodsname varchar(50),
companyname varchar(100)
)
–建立子表临 时表
create table #detail
(
orderseqno varchar(36),
detailid uniqueidentifier,
unitprice decimal(12,2),
qty int
)
–插入主表数据到主表临时表
insert into #temp
select oo.rownumber, oo.orderseqno, oo.goodsname, oo.companyname from
(select row_number () over (order by oi.createdate desc) as rownumber,
oi.orderseqno, oi.goodsname ,ci.companyname
from orderinfo oi inner join companyinfo ci on oi.companyid=ci.companyid
where oi.createdate<getdate()
) as oo
where rownumber between 10 and 20
–定义游标
declare @temp_cursor cursor
–给游标赋值
set @temp_cursor=cursor for select #temp.orderseqno,#temp.goodsname from #temp
–定义游标循环过程中所需保存的临时数据
declare @orderseqno varchar(36),@goodsname varchar(50)
–打开游标
open @temp_cursor
fetch next from @temp_cursor into @orderseqno,@goodsname
–循环游标，查询子表数据，然后插入子表临时表
while @@fetch_status=0
begin
insert into #detail
select od.orderseqno,od.orderdetailid, od.unitprice,od.qty
from orderdetail od
where od.orderseqno=@orderseqno
fetch next from @temp_cursor into @orderseqno,@goodsname
end
–关闭游标
close @temp_cursor
deallocate @temp_cursor
select * from #temp
select * from #detail
–删除临时表
drop table #temp
drop table #detail
上面的t-sql只在sql server 2005上调试成功。