sqlsql-serversql-except

Is there a way in SQL Server to show the differences in fields between two tables that have an identical layout and share some common data

I have two tables on two databases (two different orgs.) that have an identical layout - they are material master tables. One has about 600k materials listed and one has about 100k - they share about 35k of the same materials. The issue is one org may have a different attribute listed than the other one i.e. the price may be different in one or the season may be different in one versus the other. I am trying to find a way if possible to show side by side, where there are differences between a material in one org versus the other. I have come up with only the code below so far - it is using EXCEPT but it seems like it can only return the data from one row or the other whereas I am trying to see both. Any thoughts?

I have tried as mentioned above using the EXCEPT statement but only getting one side of the results. 

select * from pdx_sap_user..vw_mm_material 
where material in (select material

from pdx_sap_user..vw_mm_material 

where material  in (select material from usts_user..vw_mm_material)) -- used to get to the shared materials

EXCEPT 

select * from usts_user..vw_mm_material

I would like to get the shared 35k materials and be able to show where they are different. For reference there are about 300 columns in these tables so listing each out may be a bit of a pain.

Sample Data:

enter image description here

Solution

  • You can select the common materials from each organizations tables and add an organization pseudo column. Then just union the two queries together and sort them so the rows line up one after another.

    DECLARE @og1Mats TABLE (
    Material INT, Description NVARCHAR(50), Color NVARCHAR(50), MSRP INT
)

DECLARE @og2Mats TABLE (
    Material INT, Description NVARCHAR(50), Color NVARCHAR(50), MSRP INT
)

INSERT INTO @og1Mats VALUES (11040, 'World Cup', 'black', 100),(11050, 'Fabric', 'yellow', 10),(11060, 'Steel', 'gray', 50);
INSERT INTO @og2Mats VALUES (11040, 'World Cup', 'black', 120),(11030, 'Concrete', 'gray', 10),(11060, 'Steel', 'black', 55);

WITH common AS (
    SELECT o1.Material FROM @og1Mats o1
    INNER JOIN @og2Mats o2 ON o1.Material=o2.Material
)
SELECT o1.*, 1 as organization
FROM @og1Mats o1
INNER JOIN common c ON c.Material=o1.Material
UNION ALL 
SELECT o2.*, 2 as organization
FROM @og2Mats o2 
INNER JOIN common c ON c.Material=o2.Material
ORDER BY
    Material
    , organization

    That will get you an output similar to your example data with rows grouped by material and organization. You could then use that to compare the data either manually or with some other tool.