CODEWITHSUNDEEP
sqlsql-server-2008
Morten
Morten

Reputation: 3844

How to compare two records

I have two tables containing a new and an old dataset. I want to see if something happened in the individual records.

OLD TABLE:

ROW ID  FIELD_A FIELD_B FIELD_C
1   101 A       B       C
2   102 AA      BB      CC
3   103 AAA     BBB     CCC

NEW TABLE:

ROW ID  FIELD_A FIELD_B FIELD_C
711 101 A       B       C
712 102 AAXXXXX BB      CC
713 103 AAA     BBB     CCC

EXPECTED OUTPUT:

ROW ID  FIELD_A FIELD_B FIELD_C
712 102 AAXXXXX BB      CC

I want to be able to identify the difference in the record with id =102. Note, that my real life record is much bigger with MANY columns that need to be compared. What is the best way to do this comparison?

Upvotes: 1

Views: 194

Answers (6)

kevinm
kevinm

Reputation: 475

To find the record that has changed in your new table just use the following query

SELECT * FROM newtable EXCEPT SELECT * FROM oldtable;

If you are sure of the columns which would be having changes, you could specify those columns in the SELECT and make the query more efficient.

Upvotes: 0

Wouter van der Houven
Wouter van der Houven

Reputation: 448

select * from NewTable
except
select * from OldTable

Ways to compare and find differences for SQL Server tables and data

Its a way, if its the best one I am not sure.

Upvotes: 3

harpo
harpo

Reputation: 43158

Assuming that the tables have identical structures, this query will output a query for each column that will give you an A/B comparison of the differences:

SELECT 'SELECT a.' + COLUMN_NAME +
       '     , b.' + COLUMN_NAME +
       '  FROM old_table a' + 
       '     , new_table b ' +
       ' WHERE a.id = b.id ' +
       '   AND a.' + COLUMN_NAME + 
       '    <> b.' + COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'old_table'
   AND COLUMN_NAME <> 'id'

You can copy the output from this into a new query window and run it.

If all you care about whether there is a difference, the EXCEPT-based answer is much simpler and faster. This will tell you what the actual differences are.

Upvotes: 0

bhupendra patel
bhupendra patel

Reputation: 3179

select * from oldtable a inner join newtable b on a.id=b.id
where a.fielda<> b.fielda or a.fieldb<>b.fieldb or  a.fieldc<> b.fieldc

Upvotes: 0

SMS
SMS

Reputation: 462

By using query, below will give the records from new table if columns values does not match with old table. You need to add all the columns in the query

SELECT N.FIELD_A, N.FIELD_B, N.Field_C FROM NewTable N
LEFT JOIN OldTable O ON 
    O.FIELD_A = N.FIELD_A AND O.FIELD_B = N.FIELD_B AND O.FIELD_C = N.FIELD_C
WHERE O.FIELD_A IS NULL

If you have more columns in the table, can you try adding a column like ModifiedDate in both tables. Update the datetime whenever changes are made to the table. By this way you dont need to compare all the columns, only just compare the dates.

Upvotes: 0

Murugavel
Murugavel

Reputation: 279

If you want to get only matching rows use the following query

SELECT * FROM newtable INTERSECT SELECT * FROM oldtable

If yuo want to get only different record use the following query

SELECT * FROM newtable EXCEPT SELECT * FROM oldtable

Upvotes: 2

Related Questions