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
- 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:
Expand|Select|Wrap|Line Numbers
- EXEC addEmailNew 'TestieTesterson@notarealsite.com','Testie Testerson', '1/1/2008'
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.