Cursor alternative - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cursor alternative

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2012-09-07 : 14:49:59

Hello Guys,

Happy Friday. I was wondering if I can replace below cursor with CTE (or something else) which will be faster.

--Table 1:
test
----------
eid email

1 a@a.com
2 b@a.com
..........

eid is an identity column.

--Table 2:
test1
-------------
id email

1 a@a.com
2 b@a.com
3 c@a.com
4 d@a.com
..........

--query

declare ecurs cursor for select email from test1 order by id
open ecurs
declare @email varchar (200)
fetch next from ecurs into @email
while @@FETCH_STATUS = 0
begin

if not exists (select eid from test where email = @email)
insert into test (email)
select @email
fetch next from ecurs into @email
end
close ecurs
deallocate ecurs
go

I need to do this for lots of records and cursor is taking too long to complete.

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-07 : 14:59:14

You should be able to use either of the two queries below. If there are no duplicates in test1, you don't need the distinct keyword. It does not guarantee order of insertion though:

-- 1
INSERT INTO test
( email )
SELECT DISTINCT
	t1.email
FROM
	test1 t1
WHERE
	t1.email NOT IN
	( SELECT t.email FROM test t);
	
-- 2	
INSERT INTO test
( email )
SELECT DISTINCT
	t1.email
FROM
	test1 t1
	LEFT JOIN test t ON t.email = t1.email
WHERE
	t1.email IS NULL;