Method 1: Using a cursor
declare cursor1 cursor local for select * from tablename
open cursor1
fetch next from cursor1 into #temptable
while @@fetch_status = 0
begin
if exists (select column from tablename where
id = (select id from #temptable))
begin
/* do stuff here */
end
else
begin
/* do other stuff here */
end
if object_id('tempdb..#temptable') is not null
drop table #temptable
fetch next from cursor1 into #temptable
end
close cursor1
deallocate cursor1
Method 2: Using a temporary table with primary key
(via: http://support.microsoft.com/kb/111401)
declare @au_id char( 11 )
set rowcount 0
select * into #mytemp from authors
set rowcount 1
select @au_id = au_id from #mytemp
while @@rowcount <> 0
begin
set rowcount 0
select * from #mytemp where au_id = @au_id
delete #mytemp where au_id = @au_id
set rowcount 1
select @au_id = au_id from #mytemp<BR/>
end
set rowcount 0
Method 3: Using a temporary table without primary key
(via: http://support.microsoft.com/kb/111401)
set rowcount 0
select NULL mykey, * into #mytemp from authors
set rowcount 1
update #mytemp set mykey = 1
while @@rowcount > 0
begin
set rowcount 0
select * from #mytemp where mykey = 1
delete #mytemp where mykey = 1
set rowcount 1
update #mytemp set mykey = 1
end
set rowcount 0
