Answer to avoiding Duplicates with an INSERT

[ryushinyama]

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).

CREATE PROC addEmailNew	
@newEmail varchar(75),
@newName varchar(75),
@newDate datetime

AS
INSERT INTO tblNewsletter (emailAddress,nlName,signupDate) 
SELECT @newEmail,@newName,@newDate
FROM dual
WHERE NOT EXISTS (SELECT emailAddress FROM tblNewsletter WHERE emailAddress = @newEmail)

So if you were to run:

EXEC addEmailNew 'TestieTesterson@notarealsite.com','Testie Testerson', '1/1/2008'

Then the proc would check to see if there was an email for TestieTesterson @notarealsite.c om 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.

[iburyak]

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.

[ryushinyama]

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

[ck9663]

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

[iburyak]

CK9663 is absolutely right, you do need a unique index no mater what you do.
To insert with not EXIST use following:

IF NOT EXISTS (SELECT emailAddress FROM tblNewsletter WHERE emailAddress = @newEmail)
  BEGIN
     INSERT INTO tblNewsletter (emailAddress,nlName,signupDate) 
     Values(@newEmail,@newName,@newDate)
  END

Go to Oracle forum for more help.

Good Luck.