SQLTeam.com | Weblogs | Forums

Value comparing on each column between two table


#1

i have a query which will take au_ID as a key and check for difference exists on each column
of two table.

is it possible to modify a query to know if the key column au_ID) itself is not present
and in such case it just printed new column existing in table authors or table authors copy under column called as changed.

Please suggest The below query give

query

DECLARE @temp TABLE(au_id VARCHAR(11) PRIMARY KEY) /*this holds the primary keys of rows that have changed */
INSERT INTO @Temp(au_ID) --determine which rows have changed
SELECT au_ID
FROM --use the EXCEPT technique qhich is the quickest in our tests
(
SELECT au_id, au_lname, au_fname, phone, [address], city, state, zip, [contract]
FROM authors
EXCEPT
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract
FROM authorsCopy
)f--now we just SELECT those columns that have changed
SELECT lefthand.au_id,lefthand.name,lefthand.value AS original,Righthand.value AS changed
FROM (--now we just lay out the two tables as key value pairs, using the string versions of the data
SELECT authors.au_id, 'au_lname' AS 'name',au_lname AS 'value'
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
SELECT authors.au_id, 'au_fname' AS 'name',au_fname AS 'value'
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
SELECT authors.au_id, 'phone',phone
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
SELECT authors.au_id, 'address',address
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
SELECT authors.au_id, 'City' AS 'name',City AS 'value'
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
SELECT authors.au_id, 'State',state
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
SELECT authors.au_id, 'zip',zip
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id
UNION
SELECT authors.au_id, 'contract',CONVERT(CHAR(1),contract)
FROM authors INNER JOIN @Temp altered ON altered.au_id=authors.au_id) LeftHand
INNER JOIN (
SELECT authorsCopy.au_id, 'au_lname' AS 'name',au_lname AS 'value'
FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id
UNION
SELECT authorsCopy.au_id, 'au_fname',au_fname
FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id
UNION
SELECT authorsCopy.au_id, 'phone',phone
FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id
UNION
SELECT authorsCopy.au_id, 'address',address
FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id
UNION
SELECT authorsCopy.au_id, 'City' AS 'name',City AS 'value'
FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id
UNION
SELECT authorsCopy.au_id, 'State',state
FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id
UNION
SELECT authorsCopy.au_id, 'zip',zip
FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id
UNION
SELECT authorsCopy.au_id, 'contract',CONVERT(CHAR(1),contract)
FROM authorsCopy INNER JOIN @Temp altered ON altered.au_id=authorsCopy.au_id) rightHand
ON lefthand.au_ID=righthand.au_ID
AND lefthand.name=righthand.name
WHERE lefthand.value<>righthand.value

Thanks


#2

I don't see any column named appid


#3

Hello GBritton: i have edited the request. i apologized for typo error. Thanks a lot


#4

Don't have any sample data to test it with, but something like this should perform better and likely be easier to maintain:

SELECT a.au_ID, ca1.name, ca1.original, ca1.changed
FROM authors a
INNER JOIN authorsCopy ac ON ac.au_ID = a.au_ID
CROSS APPLY (
    VALUES('au_lname', a.au_lname, ac.au_lname),
          ('au_fname', a.au_fname, ac.au_fname),
          ('phone', a.phone, ac.phone),
          ('address', a.address, ac.address) --,...
) AS ca1(name, original, changed)
WHERE
    CHECKSUM(a.au_id, a.au_lname, a.au_fname, a.phone, a.[address], a.city, a.state, a.zip, a.[contract]) <>
    CHECKSUM(ac.au_id, ac.au_lname, ac.au_fname, ac.phone, ac.[address], ac.city, ac.state, ac.zip, ac.[contract]) AND
    (ca1.original <> ca1.changed OR (ca1.original IS NULL AND ca1.changed IS NOT NULL) OR 
    (ca1.original IS NOT NULL AND ca1.changed IS NULL))

#5

I wonder, for performance, if it would be worth keeping (perhaps as a COMPUTED column??) the CHECKSUM in the record? Should make for a nice speedy comparison ...


#6

P.S. I suppose another route (although I expect you've considered it) is to have a ModifiedDateTime column on the table and use a TRIGGER to set that on each INSERT/UPDATE. Then only necessary to compare rows where ModifiedDateTime is newer (e.g. than last time the comparison was run, or compared to the Archive table's ModifiedDateTime). The rows might still be the same, but that would catch 100% of changes where I suppose that there is a minute (how minute??) risk that CHECKSUM might give the same value for two rows that were actually different.


#7

Hello Scott,

this only give the difference when appid present in both the table. but i am trying to find out even when appid is not present in 1 table and present in other table. in that case just print message in alias column say message 'New Record exist in author' or New Record exist in authorcopy. Please suggest.


#8

That's what SCD is for in SSIS


#9

I copied your original INNER JOIN. For that, you'd need it to change it to a LEFT OUTER JOIN and change the CROSS APPLY to an OUTER APPLY (to be safe). You'd also need to add ISNULL() or COALESCE() to all the columns in the SELECT.