Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    10

    Unanswered: how to compare each row in two tables

    Hi,

    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.

    For example,

    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.

    How to create this script?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Do these tables have primary keys?

  3. #3
    Join Date
    Jul 2012
    Posts
    10
    Yes. they have the PK and same columns' names

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    This should work, but make sure you have a good backup of the data before you take an update query off the internet....

    Code:
    merge into table1
    using table2
    on table1.PK = table2.PK
    when matched then
    	update
    	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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •