Compare 2 tables for changes and load the changes and non changes into target

Hi Forum,

I have Table_A ,Table_B with each around 4million records and both have a common primary key Code_ID , table_A is 3 years old and Table_B is latest data. I need to find the changes and non-changes and load a clean copy into a new table Table_C. Each table got 4million rows and 12 columns to compare on CODE_ID, which is the best method to work on it. I tried using lookup but it failed, i tried using a T-SQL script but the tempdb was full. i had indexes created on the columns too. 

-
--SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
--FROM
--(
--  SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
--  FROM A
--  UNION ALL
--  SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
--  FROM B
--) tmp
--GROUP BY ID, COL1, COL2, COL3 ...
--HAVING COUNT(*) = 1
--ORDER BY ID

Im using SQL2005 , 

please give me some direction on how it is done with steps.

thanks in advance..

I would try using a Merge statement. Here's another article on using Merge to detect changes between tables. I hope this helps.

Hi Dustin

 

Can i use Merge in SQL 2005 ? i thought it was only for 2008. 

Ah you are correct. I missed that detail. I'll look up an example of recreating merge logic in 2005 an post what I find here.

Hi Friend,

There is a lot of way that we can accomplish this task in SSIS or Through SQL ...but you need to use SQL 2008...

Friend, Sorry to say this..because i don't have any idea about SQL 2005 since i have not used yet..