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