I currently have a query that looks like this:

  Select val1, val2, val3, val4 from Table_A where someID = 10
  UNION
  Select oth1, val2, val3, oth4 from Table_B where someId = 10

I initially run this same query above but with EXCEPT, to identify which ID's are returned with differences, and then I do a UNION query to find which columns specifically are different.

My goal is to compare the values between the two tables (some columns have different names). And that's what I'm doing.

However, the two queries above have about 250 different field names, so it is quite mundane to scroll through to find the differences.

Is there a better and quicker way to identify which column names are different after running the two queries?

EDIT: Here's my current process:

DROP TABLE IF EXISTS #Table_1
DROP TABLE IF EXISTS #Table_2

SELECT 'Dave' AS Name, 'Smih' AS LName, 18 AS Age, 'Alabama' AS State
INTO #Table_1

SELECT 'Dave' AS Name, 'Smith' AS LName, 19 AS Age, 'Alabama' AS State
INTO #Table_2

--FInd differences

SELECT Name, LName,Age,State FROM #Table_1
EXCEPT 
SELECT Name, LName,Age,State FROM #Table_2

--How I compare differences

SELECT Name, LName,Age,State FROM #Table_1
UNION
SELECT Name, LName,Age,State FROM #Table_2

Is there any way to streamline this so I can get a column list of differences?

CodePudding user response：

Here is a generic way to handle two tables differences.

We just need to know their primary key column.

It is based on JSON, and will work starting from SQL Server 2016 onwards.

SQL

-- DDL and sample data population, start
DECLARE @TableA TABLE (rowid INT IDENTITY(1,1), FirstName VARCHAR(100), LastName VARCHAR(100), Phone VARCHAR(100));
DECLARE @TableB table (rowid int Identity(1,1), FirstName varchar(100), LastName varchar(100), Phone varchar(100));

INSERT INTO @TableA(FirstName, LastName, Phone) VALUES
('JORGE','LUIS','41514493'),
('JUAN','ROBERRTO','41324133'),
('ALBERTO','JOSE','41514461'),
('JULIO','ESTUARDO','56201550'),
('ALFREDO','JOSE','32356654'),
('LUIS','FERNANDO','98596210');

INSERT INTO @TableB(FirstName, LastName, Phone) VALUES
('JORGE','LUIS','41514493'),
('JUAN','ROBERTO','41324132'),
('ALBERTO','JOSE','41514461'),
('JULIO','ESTUARDO','56201551'),
('ALFRIDO','JOSE','32356653'),
('LUIS','FERNANDOO','98596210');
-- DDL and sample data population, end

SELECT rowid
      ,[key] AS [column]
      ,Org_Value = MAX( CASE WHEN Src=1 THEN Value END)
      ,New_Value = MAX( CASE WHEN Src=2 THEN Value END)
FROM (
        SELECT Src=1
              ,rowid 
              ,B.*
         FROM @TableA A
         CROSS APPLY ( SELECT [Key]
                             ,Value
                       FROM OpenJson( (SELECT A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES)) 
                     ) AS B
        UNION ALL
        SELECT Src=2
              ,rowid 
              ,B.*
         FROM @TableB A
         CROSS APPLY ( SELECT [Key]
                             ,Value
                       FROM OpenJson( (SELECT A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES)) 
                     ) AS B
      ) AS A
GROUP BY rowid,[key]
HAVING MAX(CASE WHEN Src=1 THEN Value END)
     <> MAX(CASE WHEN Src=2 THEN Value END)
ORDER BY rowid,[key];

Output