Checking for duplicate clients before INSERT

Question

Checking for duplicate clients before INSERT

PhilipC

Hall of Fame

Points: 3124
More actions
February 5, 2018 at 1:41 am

#343890

I'm involved in developing a client based application, and I'm working through how to do a duplicate check prior to the user saving a new client record.
What I was thinking was something along the lines of:
* Check for Exact match on FirstName, LastName, DOB
* Check for Exact match on FirstName, LastName and DOB within the same month
How do you all handle duplicate checks?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That's a discussion that needs to be had with the business users. It's business rules around what's valid and invalid data,

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PhilipC Hall of Fame Points: 3124 More actions · Answer 2

Thanks Gail, that's a fair enough point.
I'm interested in what checks others have put in place around situations like this though.

Zidar SSChasing Mays Points: 651 More actions · Answer 3

Once you know the rules, you set a UNIQUE constraint on the target table and stop worrying. However, the need to prevent attempt to enter duplicates on teh front end does exist. What I have been doing in cases like that is:

1) set up UNIQUE constraint on the table
2) do not check BEFORE you send the data to be inserted, let it go as is. Most of the time all will be well - people do not introduce duplicates too often. This way, most of entries (are not duplicates) will not be slowed down.
3) if we really have a duplicate, let SQL server react and capture the error (number, description). If the error says "Duplicate' then you may want to didsplay appropriate message to the client. Often, it is helpful to tell the user where the 'first copy' is. In that case you do teh search and send response to the user. This way, you do not do search on every entry, you do it only when there is attempt to insert duplicate.

The same tactic helps with avoiding FOREIGN KEY errors.
Protect the data on the table level, do not check data on insert, simply wait for response by teh system. Even if you do not know how to read the message from the server, duplicates will be prevented, no code needed.

The best code is no code at all.

🙂

Michael L John One Orange Chip Points: 27060 More actions · Answer 4

PhilipC - Monday, February 5, 2018 1:41 AM
I'm involved in developing a client based application, and I'm working through how to do a duplicate check prior to the user saving a new client record.
What I was thinking was something along the lines of:
* Check for Exact match on FirstName, LastName, DOB
* Check for Exact match on FirstName, LastName and DOB within the same month
How do you all handle duplicate checks?

I doubt that these checks would be valid for checking duplicates. In a short time, you would get duplicates that actually are different persons.

The only real solution we ever came up with, and implemented, was to use an auto-generated number as the key for the client, and testing a variety of the fields as for duplicates, and sending the user a message stating a duplicate POSSIBLY exists. The application displayed the matches and the user would then need to decide if it was an actual duplicate.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

steve.johnson 97721 Valued Member Points: 73 More actions · Answer 5

You could use a MERGE .... WHEN NOT MATCHED THEN INSERT

PhilipC Hall of Fame Points: 3124 More actions · Answer 6

Thanks for the responses Zidar / Michael - appreciate it 🙂