Test for duplicates before inserting

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 2005 Forums

Transact-SQL (2005)

Test for duplicates before inserting

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-11-23 : 06:24:29

Hello,
I have a busy table that has groups of numbers ranging from 1 to 10,000 numbers.

Whats the best way to test for duplicates before inserting a value ?

Do I run an


IF EXISTS (Select number from registerednumbers where username = @user and group = @groupID)
begin
---code
end

else
begin
insert into registerednumbers.....
end

Is this the best way ??? Bearing in mind, this is a very busy table

Please advice

Thanks

_____________________

Yes O !

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-11-23 : 10:16:35

I prefer using a left outer join, where the outer value is null.

If it is not practically useful, then it is practically useless.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-23 : 12:29:50

do you mean you want to test for presence of a value and if found, update details and else insert?

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-11-24 : 05:03:57

quote:
Originally posted by visakh16

do you mean you want to test for presence of a value and if found, update details and else insert?

Yes please

_____________________

Yes O !

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-11-24 : 05:04:13

quote:
Originally posted by blindman

I prefer using a left outer join, where the outer value is null.

If it is not practically useful, then it is practically useless.

How do you do that ?

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 05:22:16

quote:
Originally posted by mary_itohan

quote:
Originally posted by visakh16

do you mean you want to test for presence of a value and if found, update details and else insert?

Yes please

_____________________

Yes O !

then you can do like this also

UPDATE  registerednumbers 
SET field1=value1,
field2=value2,...
WHERE username = @user 
and group = @groupID

IF @@ROWCOUNT =0
BEGIN
 INSERT INTO registerednumbers(username,group,....)
 VALUES (@user,@groupID,...)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources