check for duplicates - 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 2000 Forums

Transact-SQL (2000)

check for duplicates

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-20 : 20:39:41

I have a table with a column called "nameOnline". I have not allowed duplicate names in this field through the software application, but I have now reason to believe there are some accumulating.

How can I check to see if there are rows in the database of the same value in the column [which is a varchar(15)]

And also, for the future can I put a unique constraint on this column, even tho it is not the identity column?

Thanks,
MIke

marileng
Starting Member

28 Posts

Posted - 2002-03-20 : 21:45:32

select column, count(*)
from table
group by column having count(*) > 1
order by column

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-20 : 21:53:36

Hi Mike

Check the FAQ, there is a good article here on duplicates somewhere.
A unique constraint will work, it will throw an error when you try to violate it. Another way that I like to use, that gives me a little more flexability, is using an EXISTS. I might do a stored proc like this....



Create Procedure AddUser
	@UserName VarChar(50),
	@UserID int OUTPUT
AS

IF Exists (Select UserID FROM Users WHERE UserName = @UserName )
BEGIN
	SET @UserID = 0
	Return
END

--if you get here, you have a unique name

INSERT INTO Users (UserName) VALUES (@UserName)
SELECT @UserID = @@Identity

So if 0 is returned to the output parameter I know I have a duplicate and it wasn't inserted, I handle this in my ASP page. If I get a number greater than 0, then it has inserted and I have the new user ID.

Hope that helps

Damian

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-20 : 21:56:04

thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources