[dba-SQLServer] Append only records where some fields not in the table already

I assume that the answer will be a "not in" kind of query.  I am wondering if an index or a 
constraint could prevent an append of individual records while not causing the append to abort?  In 
either case is it going to be faster to have an index on the destination table and put up with the 
delay caused by the update to the index as new records append in, or will it be faster for the logic 
that does the record compare to just do table scans.  Think millions of records in the tables 
appending in, numbers of duplicates unknown.

John W. Colby
www.ColbyConsulting.com


jwcolby wrote:
> I have a set of 5 tables.  These tables contain names and number of children in age ranges.  As a 
> first pass at processing, I need to merge the 5 tables into one table such that the name / address 
> only gets into the table one time, IOW every record in the first table, but only the records in 
> table 2 where the name / address data is not already in the destination table.
> 
> SQL Server 2005.  Clustered index on the name / address / zip5 / zip4 of the source tables.
> 
> I have not been able to find an append syntax that will append the records not in the table but 
> prevent appending those already in, without aborting the entire append operation.
> 
> What is the magic key here?
>