I have two the same structure tables. One is the source table (table1), another one is the destination table (table2).
Now I want to compare these two table row by row.
If table1 row1 is the same as table2 row1, go to next
If table1 row2 is not the same as table2 row2, update table2 row2 to make it the same as table1 row2.
If table1 row3 is not existed in table2 row3, insert table1 row3 into table2 blank new row.
This should work, but make sure you have a good backup of the data before you take an update query off the internet....
merge into table1
on table1.PK = table2.PK
when matched then
set column1 = table2.column1,
column2 = table2.column2,
column3 = table2.column3,
when not matched by target then
insert (column1, column2, column3, ...)
values (table2.column1, table2.column2, table2.column3, ...);
Naturally, you will need to supply your own column names. The PK column(s) do not need to be in the update, but they will need to be in the insert. This will only work in SQL 2008 and higher.