Sometimes you are suppose to change logic of your procedure/function which results a table and then you have to compare the tables, whether the logical modification caused any change in your table. If you are facing any scenario like this, then here i am sharing my experience. So lets begin with a table “Address_user”,which contains user address shown below in screenshot.
SELECT au.AddressID,au.AddressLine1,au.City,au.PostalCode FROM Address_User au
Assume that we have got some changes in logic of procedure/function, so first of all we will take backup of the table like shown in below query.
SELECT * INTO Address_User_backup FROM Address_User au
SELECT * INTO Address_User_backup FROM Address_User au
Now both will be same in the result, lets compare. There are many ways to compare two tables, here i will show you couple of ways like
- Using EXCEPT keyword
- Using Checksum
- Using NOT IN
- Using union all
Using Except Keyword:
Its very simple just you need to use Except keyword between select query of two tables like shown below. this returns any distinct values from the left query that are not found on the right query.
SELECT au.AddressID,au.AddressLine1,au.City,au.PostalCode FROM Address_User au
EXCEPT
SELECT aub.AddressID,aub.AddressLine1,aub.City,aub.PostalCode FROM Address_User_backup aub
EXCEPT
SELECT aub.AddressID,aub.AddressLine1,aub.City,aub.PostalCode FROM Address_User_backup aub
This wont result you anything, as both tables are exactly same.
Lets modify the table now , i am updating city ‘Portland’ with ‘Kochi’.
UPDATE Address_User
SET City='Kochi'
WHERE City='Portland'
SET City='Kochi'
WHERE City='Portland'
Now lets compare again using same query. You can see the result below, which displays city Kochi, which is there in left side query but not in right side of Except.
Lets swap the select queries and then compare the result. you can see it results Portland instead of Kochi. it is because backup table is having city Portland which is not there in Right-side query of EXCEPT Keyword.
SELECT aub.AddressID,aub.AddressLine1,aub.City,aub.PostalCode FROM Address_User_backup aub
EXCEPT
SELECT au.AddressID,au.AddressLine1,au.City,au.PostalCode FROM Address_User au
EXCEPT
SELECT au.AddressID,au.AddressLine1,au.City,au.PostalCode FROM Address_User au
Using Checksum:
If you are looking for faster way and exact match then you can use this method.
IF (
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM Address_User)=(
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM Address_User_backup)
SELECT 'they are probably the same'
ELSE
SELECT 'they are different'
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM Address_User)=(
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM Address_User_backup)
SELECT 'they are probably the same'
ELSE
SELECT 'they are different'
Using NOT IN:
If you are not having duplication in data then you can choose this method.
SELECT * FROM Address_User WHERE
AddressID NOT IN (SELECT AddressID FROM Address_User_backup)
AddressID NOT IN (SELECT AddressID FROM Address_User_backup)
For complete explanation please refer this link.
Using union all:
Its preferable to use if you have primary key in your table.
SELECT COUNT(xyz.AddressID) FROM
(SELECT au.AddressID,au.AddressLine1,au.City,au.PostalCode FROM Address_User au
UNION ALL
SELECT aub.AddressID,aub.AddressLine1,aub.City,aub.PostalCode FROM Address_User_backup aub) xyz
GROUP BY xyz.AddressLine1,xyz.City,xyz.PostalCode
HAVING COUNT(xyz.AddressID)<>2
(SELECT au.AddressID,au.AddressLine1,au.City,au.PostalCode FROM Address_User au
UNION ALL
SELECT aub.AddressID,aub.AddressLine1,aub.City,aub.PostalCode FROM Address_User_backup aub) xyz
GROUP BY xyz.AddressLine1,xyz.City,xyz.PostalCode
HAVING COUNT(xyz.AddressID)<>2
Conclusion:
Like i explained comparing two tables can be done in many ways depending upon your requirement, but among these explained methods i preferred to use Except or checksum, because they are simpler and faster as well. other than these explained method you can use Joins as well to comparison.
References:
