Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How to detect duplicates in my SQL Server database and not create another record.

Posted on 2011-03-01
Medium Priority
Last Modified: 2012-05-11
Hello Experts, if this can be done, then this is the place to ask this question. Okay here's my situation. I have created an app that captures all the pertinent information that we need to process our clients licensing needs. By the way, we're an insurance licensing company. So, here's where things are messing up on me. For whatever reason, we are getting duplicates in our database. Now, we have some clients that call in and tell us that a certain page is not acting right for whatever reason. I can't for the life of me figure out what is going on with my app. I have filled this thing out from anywhere to a dial-up connection to a wireless connection and it ALWAYS works good for me. And from different locations, still, no problems for me. But, so they say, it's messing up on them. I don't know if there are memory leaks, I don't know, it's driving me crazy, anyway, so this is how I built the application. It's basically several "pages" and so at the bottom of each page there is a button that says "Next Page" and when the clients click that button it dumps their information from that page into a database table built for that page only. Now, I was wondering, is there a way to, I guess, like poll the database if the app messes up on them to see of their data is already there? And then if their data does exist, to NOT do a database insert a second time, but just go ahead and move them to the next page? It's really filling up my little database that I built for this, and I keep having to fire up SQL Enterprise Manager and delete the duplicates.
I already added jQuery BlockUI with a neat little "Processing data...Please wait." message to kind of slow them down a little so they won't get so impatient while waiting to move to the next page. I don't know what else to do, I have tested this thing to the hilt and I like I said, I cannot figure out what is going on. I just know I'm tired of duplicates. Also, just for the record, I'm not using any kind of Ajax or ASP.Net Ajax or anything, don't know if that matters or not.
If any of you have had this problem and came up with a solution, I would really, really appreciate it if you could pitch me a solution.


Oh one more thing, I am creating a unique userid on the first page and passing it encrypted through the query string to the next page, and then the next page, and so on like that, and it gets dumped to the database as well.
Question by:RunForrestRun
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 664 total points
ID: 35011820

Why are you storing data for each page into the database separately.

Why don't you store them in a session variable and after all the data is gathered on the last page, store everything at once. You can create a serializable object for that.


Accepted Solution

sshah254 earned 668 total points
ID: 35011884
When you are inserting data into the table, it seems like you are not checking if the data already exists.

Insert the data into a temp table - let's sa temp1

Now insert the data only if it does not exist ...

If your table is table1, then

Insert into table1
select a.* from temp1 a left join table1 b on a.something = b.something
where b.something is null ...

This way, you are sure to not have duplicates.


Author Comment

ID: 35012026
ewangoya, this used to be one LONG application and management wanted it broken out into "modules" so each page would have it's own database table. And I had sort of thought something about session variables but on some pages there's SO MUCH data that I just didn't want to do it that way.
sshah254, thanks for that, I guess I didn't think about checking first if the data existed. Some things just slip by me sometimes. ewangoya, sshah254, thanks for your replys, you both have really got me to thinking down some different avenues.
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

LVL 79

Expert Comment

ID: 35012244
Adding to the others points with your existing setup.
I.e. you would use the data collection as a "temporary storage"
One the application is complete where they fill all the pages either in a single sitting or over time. (this is where you would check if the person had previously started the process" caution should be used to make sure the person filling up is really the same person as he one who initially began the process since you would not want to reveal the information to someone else.

Once the process is complete, you would take the data from these per page "temporary tables" and arrange the data more sensibly within the permanent database structure.

Alternatively, your initial page will be for the user to create the minimal account for access.
The user once logged in will have the option to fill the additional information at will which you will then use to populate the permanent table and would not need the "temporary".
this approach will shorten the amount of time the user has to spend in one sitting.
You could limit the user's ability/access until the application process is complete.

Author Comment

ID: 35012538
Hmm, ok thanks arnold, something to think about, I really appreciate the great ideas, keep em coming.
LVL 79

Expert Comment

ID: 35012737
If you are planning on rewriting/rearranging the app, you may wish to rearchitect the database.
The duplicate detection/elimination can be done on either on both ends.
Setting up a unique index/primary keys within database tables will prevent the insertion of a duplicate record.
Checking for non existance of a record within your frontend application is the other option.

The design of the database could help.
There are draw backs the more tables the more queries and at times the queries can get complex.
If there is a single table with all the information in it, redundant information will be present i.e. city, state, country, etc. will increase the amount of "useles" data in the database.

You may want to use the ERD (database diagram in SSMS) to arrange the database
i.e. countries, states, cities, customer, etc. customer/client/insurer

Author Comment

ID: 35017995
arnold, I would really like to do it front-end if possible, being that the application is already deployed; that way I can work on my copy locally in Visual Studio, and I'm not in there messing around in SQL while the clients are trying to fill anything out. If I could figure out a way to do it in my code-behind file (VB.Net), that would be great. That way I can play around with it, and when I'm done and have it working the way I want, deploy it to the server.
LVL 79

Assisted Solution

arnold earned 668 total points
ID: 35019806
It is often better to design the database while taking into consideration the complexity of the number of queries that would need to be generated and their complexity.
You could work on enforcing/eliminating which would that you would have to check whether the user/account is present or depending on what other identifying/unique info you ask, whether such info was already provided because of a typo. etc.

You need to layout the flow of the signup/registration/initial set of entries your firm requires to establish the login and as much identifying info from the user such as:
username, email, password, Name, address, phone, etc.
Then the user has an option to login and has a reminder on the page indicating which forms they have to complete to gain whatever it is they are trying to gain access/license to operate etc.

Based on what I think your setup is, you might be better off considering starting from scratch and see whether that would simplify things for you versus trying to patch the existing app./database.

Hopefully you are extremely knowledgeable with the existing database structure and front end application. If not, you should get very familiar with the existing database structure,record flow, table relationships both explicit and implicit i.e. there is a field in two tables that contains the same information versus one having the info and the other a reference to the info.

I think trying to set up a new flow using an old database structure where you would have to both validate data and maintain data integrity in the existing structure might be a daunting task.

Author Closing Comment

ID: 35021883
ewangoya, sshah254, arnold, I want to thank all 3 of you for your answers, you all got me to thinking down some different avenues. Note: I tried to give the same number of points to all 3 of you. Good day.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question