preventing duplicate records - 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)

preventing duplicate records

Author

Topic

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-06-19 : 10:01:05

I'm still a novice with SQL so bear with me...

I have a row labeled 'MRNum_ClientID' computed as ([MRNum]+CONVERT([varchar],[ClientID],(0))).

I was on another forum discussing how I could make it so my users couldn't enter in a duplicate record in my
program I've created in Alhpa Five. It was suggested to "make an index" and then SQL would return an error. How do I go about doing this? In my program the user types in the MRNum (medical record number) and ClientID is automatically populated.

Bottom line is 'MRNum_ClientID' must always be unique.

Thank you for your help.

-Mike

Mike Brown

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-19 : 10:17:20

quote:
Originally posted by mikeallenbrown

In my program the user types in the MRNum (medical record number) and ClientID is automatically populated.

Bottom line is 'MRNum_ClientID' must always be unique.

-Mike

Mike Brown

Create a non clustered "unique" index on the combination of these two fields

Cheers
MIK

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-19 : 10:19:41

e.g.
create unique nonclusetered index index_Name
On TableName
(MRNum,ClientID)

Cheers
MIK

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-06-19 : 11:20:38

Thank you! ... but now I'm getting errors because there are existing duplicates in my table already (so it cancels).

My follow question would be how can I display a list of all the duplicates? ...and edit them of course.

-Mike

Mike Brown

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-19 : 11:35:46

Following query will give you the repeating/duplicates MRNNum and ClientID

SELECT MRNNum,ClientID,Count(1)
FROM TableName
Group by MRNNum,ClientID
Having count(1) >1

Cheers
MIK

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-06-19 : 11:54:46

Thank you again!

-Mike

Mike Brown

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-19 : 11:59:32

You're welcome

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources