June 23, 2009 at 4:04 pm
Hi all,
I have two almost similiar tables in sql server (Table1 has more columns and Table2 has few less columns) for each incoming record from Table1 I want to compare whether there is any change in data by comparing each column to column. Even if there is one change in one column out of 10 columns I want to insert that record into some other Table 3. I want to run a query to compare those two tables and to find out those rows that are not common. here are the columns for the two tables
Table 1
id,
name,
address1,
state,
telno,
zipcode,
test1,
test2,
test3
Table 2
id,
name,
address1,
state,
telno,
zipcode
There are just few columns in the table and there are more than 75 columns which are in common.
In one row everything could be similiar except the tel no, in some other row everything could be similiar except the zip code. how do i run a query to figure out which row is not similiar.
Thanks,
Sai
June 23, 2009 at 5:54 pm
There are probably quite a few ways you can accomplish this.
1. One way I've tried in the past is to create another column at the end of each table and store a checksum and/or binary_checksum (see BOL) to get a unique value for each row. However, I've run into quite a few instances where different rows have the same checksum and/or binary_checksum. So I would recommend against this option.
2. Another way to go is to use the EXCEPT operator -- new to SQL 2005.
SELECT id, name, address1, state, telno, zipcode
FROM Table1
EXCEPT
SELECT id, name, address1, state, telno, zipcode
FROM Table2
June 26, 2009 at 9:58 am
I'd go with the EXCEPT as well, you just have to be sure that both queries have the same number oc columns and that they are the ones you want to compare to as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply