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
Begin
  Declare Cursor c2 for SELECT * from Table2
  OPEN c2
  WHILE @@Fetch_Status=0
  Begin
     /*Comparison happens here*/
  END
END

When fecthing, must I specify which cursor I am fetching and how do I do that?

EDIT

For each record in Table1 I want to

• Search Table2 for that record based on the primary key.
• When it is found, I want to update the extra column values in Table2 based on the value of a column in table1.
• When this record is missing in table2, I want to copy it from table1 to table2 and set a default value of the extra column in table2.

Open to other solutions (not restricted to cursors)

回答1:

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

SELECT * from Table1
except
SELECT * from Table2

also run it in opposite way:

SELECT * from Table2
except
SELECT * from Table1

You'll see the exact set difference:

EXCEPT and INTERSECT

回答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.

回答3:

Can't you just...

SELECT * FROM Table1 LEFT JOIN Table2 ON <your matching criteria>

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