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.
Can i use Merge in SQL 2005 ? i thought it was only for 2008.
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..