SQL server 2000 编写cursor,实现从另一个表中读取数据到一个表中。

 

if exists (select id from tempdb..sysobjects
           where id = object_id('tempdb..#temp'))
drop table #temp
go

create table #temp
( 
 id   int NOT NULL IDENTITY (1, 1),
  scardno varchar(10),
  scardtype varchar(10)
)

set nocount on
declare @sql varchar(200)
declare @scardno varchar(10)
declare @scardtype varchar(10)
declare @count integer

declare cur cursor
for select scardno,scardtype,(select count(*)from tcard)as tcard_num from tcard  order by scardno  --where
--select count(*)as tcard_num from tcard
open cur
fetch next from cur  into @scardno,@scardtype,@count
while @@fetch_status=0
begin

insert into #temp( scardno,scardtype) values(@scardno,@scardtype)

fetch next from cur  into @scardno,@scardtype,@count

if((select count(*) from #temp)=@count)
break

end
deallocate cur

select * from #temp

drop table  #temp

set nocount off