Avoid Inserting Duplicate Records

Here's a quick solution to the problem where you don't want to insert duplicate records into a SQL Server table. Of course, the easier way to do this is to set up a constraint, but there are times when you can't do that. For example, if you have a table where you are using a deleted flag as opposed to doing hard deletes. If you have foreign keys to deal with, I would suggest creating a table variable (again, assuming you are using SQL Server) and inserting the denormalized records there, and then doing the insert as listed below. You could probably do it in one statement, but just because you *can* do something doesn't necessarily mean you should. The two step process would be easier to read and more clearly indicates the intent of the code. Comments are great, but code should be self-documenting.

INSERT INTO TABLE_A
SELECT field_1, field_2, field_3
 FROM TABLE_NEW_RECORDS B
WHERE NOT EXISTS
 (
 SELECT DISTINCT B.comp_1, B.comp_2, B.comp_3
  FROM  TABLE_LOOKUP LU
  WHERE B.comp_1 = LU.comp_1 And
   B.comp_2 = LU.comp_2 And 
   B.comp_3 = LU.comp_3
 )

Hope this helps.