Checking for duplicates in a table

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

General SQL Server Forums

New to SQL Server Programming

Checking for duplicates in a table

Author

Topic

JR83
Starting Member

31 Posts

Posted - 2014-08-06 : 02:58:01

HI There

I am new at this..

I would like to know how i can check for duplicate entries for example if a serial number has already been inputted and a user tries to input the same serial number.. how can i get a trigger or some sort to check for duplicates and then prompt that the number has already been entered

any help on a script would be much appreciated

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-06 : 04:40:00

add unique key on serial number column

Javeed Ahmed

JR83
Starting Member

31 Posts

Posted - 2014-08-06 : 05:23:38

Would a Trigger of some sort not work?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-06 : 05:52:47

yes,you can use triggers for this.but trigger would be a overload compared to unique key,thats why we have constraints.just use try catch in your insert script and display the error as you want.

Javeed Ahmed

JR83
Starting Member

31 Posts

Posted - 2014-08-06 : 06:33:15

if i use a unique key, it inserts a null value if there are duplicates

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-06 : 07:32:52

a unique key is a possibility. you could then trap and handle the error. you could also just look up to see if the value is already in the table first and avoid the error. I have coded an example:

-- set up
CREATE TABLE #SetUp
(ID int identity(1,1), SerialNumber Varchar(10))

INSERT INTO #SetUp
VALUES('ABC122345')

ALTER TABLE #SetUp
ADD CONSTRAINT UN1 UNIQUE (SerialNumber)

-- end set up

CREATE PROC #Test
@SerialNumber Varchar(10)
AS
BEGIN
BEGIN TRY

--IF EXISTS (SELECT 1 FROM #SetUp WHERE SerialNumber = @SerialNumber)
-- BEGIN
-- SELECT ' has been used. you must use a unique serial number. this avoids the catch'
-- RETURN;
-- END

INSERT INTO #SetUp
VALUES(@SerialNumber)

END TRY
BEGIN CATCH

SELECT CASE WHEN ERROR_NUMBER() = 2627 THEN @SerialNumber + ' has been used. you must use a unique serial number'
ELSE ERROR_MESSAGE() END

END CATCH
END

EXEC #Test 'ABC122345'

-- now with uncommenting lookup

ALTER PROC #Test
@SerialNumber Varchar(10)
AS
BEGIN
BEGIN TRY

IF EXISTS (SELECT 1 FROM #SetUp WHERE SerialNumber = @SerialNumber)
BEGIN
SELECT ' has been used. you must use a unique serial number. this avoids the catch'
RETURN;
END

INSERT INTO #SetUp
VALUES(@SerialNumber)

END TRY
BEGIN CATCH

SELECT CASE WHEN ERROR_NUMBER() = 2627 THEN @SerialNumber + ' has been used. you must use a unique serial number'
ELSE ERROR_MESSAGE() END

END CATCH
END

EXEC #Test 'ABC122345'

JR83
Starting Member

31 Posts

Posted - 2014-08-06 : 08:38:52

HI Michael

i get the following error when creating the SP

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-06 : 09:01:42

Collation issue- hopefully just setting the collation at the table will help. try swapping out the following with the previous version I psoted

CREATE TABLE #SetUp
(ID int identity(1,1), SerialNumber Varchar(10) COLLATE database_default)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources