Merging the contents of two tables without duplicating content

Question

I have two identical SQL Server tables ( SOURCE and DESTINATION ) with many columns in each. I want to insert into the DESTINATION table rows from the SOURCE table that do not yet exist in the DESTINATION table. I define equality between two rows if all columns are the same except for the timestamp, count column, and integer primary key. So I want to insert in DESTINATION all the rows in SOURCE that no longer exist in DESTINATION , ignoring the count, timestamp and primary key columns.

How to do it?

Thanks for all the contributions! I decided to use the Merge command because it is structured to update and paste in one of the statements, 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, I first called the AAA and BBB tables. I edited and changed the names AAA to SOURCE and BBB to DESTINATION for clarity

Answer 1

using the Select statement for this purpose, since Sql Server 2008 is deprecated, not Select you can use the Merge statement:

link

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 that:

 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.

Answer 3

The solution using the good ol'-fashioned LEFT JOIN is the note in the example below, only the first row of the BBB is inserted in the AAA, because only it does not have the corresponding row in the AAA. You would col1 and col2 with the actual table columns.

 > select * from AAA; +---------------------+------+------+ | timestamp | col1 | col2 | +---------------------+------+------+ | 2012-03-17 08:17:22 | 1 | 1 | | 2012-03-17 08:17:27 | 1 | 2 | | 2012-03-17 08:17:30 | 1 | 3 | | 2012-03-17 08:17:32 | 1 | 4 | | 2012-03-17 08:17:49 | 2 | 2 | | 2012-03-17 08:17:52 | 2 | 3 | | 2012-03-17 08:17:54 | 2 | 4 | +---------------------+------+------+ 7 rows in set (0.00 sec) > select * from BBB; +---------------------+------+------+ | timestamp | col1 | col2 | +---------------------+------+------+ | 2012-03-17 08:18:16 | 2 | 1 | | 2012-03-17 08:18:18 | 2 | 2 | | 2012-03-17 08:18:20 | 2 | 3 | +---------------------+------+------+ 3 rows in set (0.00 sec) > INSERT INTO AAA SELECT BBB.* FROM BBB LEFT JOIN AAA USING(col1,col2) WHERE AAA.timestamp IS NULL; > select * from AAA; +---------------------+------+------+ | timestamp | col1 | col2 | +---------------------+------+------+ | 2012-03-17 08:17:22 | 1 | 1 | | 2012-03-17 08:17:27 | 1 | 2 | | 2012-03-17 08:17:30 | 1 | 3 | | 2012-03-17 08:17:32 | 1 | 4 | | 2012-03-17 08:17:49 | 2 | 2 | | 2012-03-17 08:17:52 | 2 | 3 | | 2012-03-17 08:17:54 | 2 | 4 | | 2012-03-17 08:18:16 | 2 | 1 | +---------------------+------+------+ 8 rows in set (0.00 sec)