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

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.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

sshah254Connect With a Mentor Commented:
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.

Ephraim WangoyaConnect With a Mentor Commented:

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.

RunForrestRunAuthor Commented:
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.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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.
RunForrestRunAuthor Commented:
Hmm, ok thanks arnold, something to think about, I really appreciate the great ideas, keep em coming.
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
RunForrestRunAuthor Commented:
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.
arnoldConnect With a Mentor Commented:
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.
RunForrestRunAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.