SQL - Audit two tables - Pulling singular column for errors

Question

I have a series of tables I have to audit against each other. Each table has a varying amount of columns anywhere from 4 to 40. The task at hand is to audit column ID in tableA and column ID in table B. etc.

my code looks like this

SELECT          mismatch_field =    
CASE            WHEN E.id <> Live.id THEN 'ID'
                WHEN E.FirstName <> Live.FirstName THEN 'FirstName'
                WHEN E.LastName <> Live.LastName THEN 'LastName'
                WHEN E.WEB_Name <> Live.WEB_Name THEN 'WEB_Name'
                END,
                E.*,    
                '<-EDI -- LIVE->',  
                Live.*
from            #upld_TEST E    
left outer join #upld_LIVE Live on E.id = Live.id
WHERE       (   E.id    <> Live.id OR 
                E.FirstName <> Live.FirstName OR 
                E.LastName  <> Live.LastName OR
                E.WEB_Name  <> Live.WEB_Name    )
ORDER BY CASE   WHEN E.id <> Live.id THEN 'id'
                WHEN E.FirstName <> Live.FirstName THEN 'FirstName'
                WHEN E.LastName <> Live.LastName THEN 'LastName'
                WHEN E.WEB_Name <> Live.WEB_Name THEN 'WEB_Name'
                END

It's pretty straightforward. Find me the fields that don't match, and report the column that doesn't match and the remainder of the columns for review.

What i'm looking to do is have the output be a little simplified. If E.FirstName <> Live.FirstName, I want the output to read FirstName then show me the Test field in a column and Live field in the next column so it looks something like this (and will require a lot less scrolling and looking)

#Upld_Test  Record_id   Upld_Test           Upld_Live
ID          12          626                 231
FirstName   24          John                Mark
FirstName   55          Sam                 Jani
WebName     11          Lake Smith          Lake Smith's

The Record_Id is just an identifier that i want to appear, but the concern is instead of having all the columns from E table and all the columns from the Live table to appear and then i scroll to find the culprits only show me the incorrect ones.

How do i change my statement.

** Part two would be is there a way to make this super dynamic that the #temp_tables in question can be set up as variables at the top. So that i can change them as I said lots of tables to audit. and use one query *** Thank you.

Answer 1

You could use cross apply. It is bit tricky because you need to align the datatypes - I don't know what they are so I used varchar(max) everywhere:

select v.*
from #upld_TEST e
inner join #upld_live l on u.id = l.id
cross apply (values 
    (
        e.id, 
        'id', 
        cast(e.id as varchar(max)), 
        cast(l.id as varchar(max))
    ),
    (
        e.id, 
        'FirstName', 
        cast(e.FirstName as varchar(max)), 
        cast(l.FirstName as varchar(max))
    ),
    (
        e.id, 
        'LastName', 
        cast(e.LastName as varchar(max)), 
        cast(l.LastName as varchar(max))
    ),
    (
        e.id, 
        'WEB_Name', 
        cast(e.WEB_Name as varchar(max)), 
        cast(l.WEB_Name  as varchar(max))
    )
) v(record_id, col, test_val, live_val)
where v.test_val <> v.live_val