Sql: Compare Two Table Record By Record

I want to compare two tables recordby record. I have two cursor for each table. The code looks like this Declare Cursor c1 for SELECT * from Table1 OPEN c1 While @@Fetch_status=0 B

Solution 1:

If tables have same column definition, the fastes way is just use 'except' clause:

SELECT*from Table1
exceptSELECT*from Table2

also run it in opposite way:

SELECT*from Table2
exceptSELECT*from Table1

You'll see the exact set difference:

EXCEPT and INTERSECT

Solution 2:

Redgate has a great tool for this, if you'd rather just spend a few dollars:

You can get a free trial to see if it suits your needs.

Solution 3:

Can't you just...

SELECT*FROM Table1 LEFTJOIN Table2 ON<your matching criteria>

...and then perform INSERT for the rows whose right "half" is NULL and UPDATE for those whose isn't?