SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 preventing duplicate records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikeallenbrown
Starting Member

USA
47 Posts

Posted - 06/19/2013 :  10:01:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/19/2013 :  10:17:20  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/19/2013 :  10:19:41  Show Profile  Reply with Quote
e.g.
create unique nonclusetered index index_Name
On TableName
(MRNum,ClientID)

Cheers
MIK
Go to Top of Page

mikeallenbrown
Starting Member

USA
47 Posts

Posted - 06/19/2013 :  11:20:38  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/19/2013 :  11:35:46  Show Profile  Reply with Quote
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
Go to Top of Page

mikeallenbrown
Starting Member

USA
47 Posts

Posted - 06/19/2013 :  11:54:46  Show Profile  Reply with Quote
Thank you again!

-Mike

Mike Brown
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/19/2013 :  11:59:32  Show Profile  Reply with Quote
You're welcome

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000