merging content of two tables without duplicating content

Question

I have two identical SQL Server tables (SOURCE and DESTINATION) with lots a columns in each. I want to insert into table DESTINATION rows from table SOURCE that do not already exist in table DESTINATION. I define equality between the two rows if all columns match except for the timestamp, a count column, and the integer primary key. So I want to insert into DESTINATION all rows in SOURCE that dont already exist in DESTINATIONignoring count, timestamp, and the primary key columns.

How do I do this?

Thanks for all the contributions! I chose to use the Merge command since it is structured to allow for updates and inserts in one statement and I needed to do the update separately.

this is the code that worked:

Merge 
into DESTINATION as D
using  SOURCE as S
on (    
D.Col1 = S.Col1
and D.Col2 = S.Col2
and D.Col3 = S.Col3
)
WHEN MATCHED
 THEN UPDATE SET D.Count = S.Count
 WHEN NOT MATCHED THEN  
        INSERT (Col1, Col2, Col3, Count, timestamp)
        VALUES (S.Col1, S.Col2, S.Col3, S.Count, S.timestamp);

note: when I wrote this question first I called the tables AAA and BBB. I edited and changed the names of AAA to SOURCE AND BBB to DESTINATION for clarity

Answer 1

using Select statement for this purpose since Sql Server 2008 is obsolete instead of Select You can use Merge statement :

ref:

http://technet.microsoft.com/en-us/library/bb510625.aspx http://weblogs.sqlteam.com/peterl/archive/2007/09/20/Example-of-MERGE-in-SQL-Server-2008.aspx

Answer 2

Something like this:

INSERT INTO BBB(id, timestamp, mycount, col1, col2, col3, etc.)
SELECT id, timestamp, mycount, col1, col2, col3, etc.
   FROM AAA
   WHERE
       NOT EXISTS(SELECT NULL FROM BBB oldb WHERE
          oldb.col1 = AAA.col1
          AND oldb.col2 = AAA.col2
          AND oldb.col3 = AAA.col3
       )

Add columns as needed to the NOT EXISTS clause.