469,632 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,632 developers. It's quick & easy.

Answer to avoiding Duplicates with an INSERT

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
4 6198
iburyak
1,017 Expert 512MB
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
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
2,878 Expert 2GB
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
1,017 Expert 512MB
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.

Similar topics

4 posts views Thread by Frank-René Schäfer | last post: by
1 post views Thread by cefrancke | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.