By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 941 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Answer to avoiding Duplicates with an INSERT

P: 23
I had to do a lot of searching to get this one to work and in doing so I saw a lot of different sites where people were looking for this answer so I thought I would put it up.

If you are trying to put data in to a table where you don't want any duplicate data a primary key, unique constraint and all that prevention is the best way to go. However when you try to insert data and it conflicts with an entry you get an error then you have to deal with error handling. So as a step to keep from getting these errors you can use a second table that will allow you to enter in values with a SELECT statement even if the values are not in the table you are selecting from. This will in turn allow you to use the NOT EXISTS clause. Another place where this is useful is if your table already has duplicate data and you want to stop this problem from compounding while you are working on removing the duplicates.

So first you will need to create a table, I called mine DUAL as it is the name of a default table in Oracle DBs that has a very similar use (so I've read). In this table you have one row and one column. I have the column USED though it can be anything you like. It has a datatype of varchar(1) and a value of x. It is important that it has a value. Didn't experiment to see if it could be other datatypes like numeric or int. So I can't say for sure if it would work but don't know why it would not.

In this example, and the reason I needed to do this in the first place, I am using a proc to enter in Newsletter info from a form on my site and when a customer opts in during the ordering process. I have listed the proc below in CREATE syntax(it has been simplified but should work just the same).

Expand|Select|Wrap|Line Numbers
  1. CREATE PROC addEmailNew    
  2. @newEmail varchar(75),
  3. @newName varchar(75),
  4. @newDate datetime
  5.  
  6. AS
  7. INSERT INTO tblNewsletter (emailAddress,nlName,signupDate) 
  8. SELECT @newEmail,@newName,@newDate
  9. FROM dual
  10. WHERE NOT EXISTS (SELECT emailAddress FROM tblNewsletter WHERE emailAddress = @newEmail)
  11.  

So if you were to run:

Expand|Select|Wrap|Line Numbers
  1. EXEC addEmailNew 'TestieTesterson@notarealsite.com','Testie Testerson', '1/1/2008'
  2.  
Then the proc would check to see if there was an email for TestieTesterson@notarealsite.com already in the table and only insert the data if there is not. No error messages or triggers just 1 row(s) affected or 0 row(s) affected.

You can use this in many other ways, and the SELECT statement used in the WHERE Clause can be as complicated as you like. It will only insert if the statement returns nothing. Another use could be that it would only insert the email if they have a record in another table. So, using this example, only people who have an account with your site will be allowed to enter their email. So the SELECT in the WHERE Clause could search for their name or account number in a customer table.

I hope this will help others out and would like to hear if it has. Also suggestions for other uses as well as better ways to do the same task would be great!!

Thanks, RSY.
Jan 8 '08 #1
Share this Question
Share on Google+
4 Replies


iburyak
Expert 100+
P: 1,017
I never use a second table to prevent duplicates.
You can use distinct at insert time or not exist close.
Unique index is a good solution.


Good Luck.
Jan 8 '08 #2

P: 23
I never use a second table to prevent duplicates.
You can use distinct at insert time or not exist close.
Unique index is a good solution.


Good Luck.

If you have the time, could you explain the syntax you would use for the INSERT statement with the DISTINCT? Also the NOT EXIST CLOSE syntax alternative would be great too!!

Though what I have written above is working for me, the methods you mention here may be a better way or work in cases where this method wouldn't.

Thanks, RSY
Jan 9 '08 #3

ck9663
Expert 2.5K+
P: 2,878
i'd still recommend that you create a unique index using those keys that could define a duplicate. during insert, check the error if your error corresponds with inserting duplicate records. in this way, even if the insert was done manually, the table will protect itself from any duplicate insertion...

-- ck
Jan 9 '08 #4

iburyak
Expert 100+
P: 1,017
CK9663 is absolutely right, you do need a unique index no mater what you do.
To insert with not EXIST use following:

Expand|Select|Wrap|Line Numbers
  1. IF NOT EXISTS (SELECT emailAddress FROM tblNewsletter WHERE emailAddress = @newEmail)
  2.   BEGIN
  3.      INSERT INTO tblNewsletter (emailAddress,nlName,signupDate) 
  4.      Values(@newEmail,@newName,@newDate)
  5.   END 
Go to Oracle forum for more help.

Good Luck.
Jan 9 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.