CODEWITHSUNDEEP
sql-server
Thomas
Thomas

Reputation: 34188

How to compare two row from two different table

i have two table with identical structure but data could be different. so i like a way to compare data between two row and display the difference in a specific way

for say this is my sample data

field name                OLD VALUE      NEW VALUE

------------             ----------      ---------

Name                       Ram           sam

age                        31            56

salary                     12000         26100

so guide me how to write the script in sql server which will show the diff above way after comparing two row. thanks

Upvotes: 0

Views: 125

Answers (2)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

Using unpivot on both tables and a full outer join on the results. would be one way and i believe this is exactly what you want:

;WITH Table1CTE AS
(
    SELECT *
    FROM 
    (
        SELECT ID,
               CONVERT(VARCHAR,Name) AS Name,
               CONVERT(VARCHAR,Age) AS Age,
               CONVERT(VARCHAR,Salary) AS Salary
        FROM   Table1 
    ) P
    UNPIVOT
   (
       Value For Field IN
      (Name,Age,Salary)
   ) AS T
), Table2CTE AS
(
    SELECT *
    FROM 
    (
        SELECT ID,
               CONVERT(VARCHAR,Name) AS Name,
               CONVERT(VARCHAR,Age) AS Age,
               CONVERT(VARCHAR,Salary) AS Salary
        FROM   Table2 
    ) P
    UNPIVOT
    (
        Value For Field IN
        (Name,Age,Salary)
    ) AS T
)
SELECT COALESCE(OLD.ID,NEW.ID) AS ID,
       COALESCE(OLD.Field,NEW.Field) AS Field,
       OLD.Value AS OldValue,
       NEW.Value AS NewValue
FROM   Table1CTE OLD
       FULL OUTER JOIN Table2CTE NEW
       ON OLD.Id = NEW.Id
           AND OLD.Field = NEW.Field

SQL Fiddle Demo

One other way would be multiple unions:

SELECT COALESCE(Old.Id,New.Id) AS ID,
       'Name' AS 'Field',
       Old.Name AS 'Old Value',
       New.Name AS 'New Value'
FROM   Table1 Old
       FULL OUTER JOIN Table2 New
           ON Old.Id = New.Id
UNION ALL
SELECT COALESCE(Old.Id,New.Id) AS ID,
       'Age' AS 'Field',
       Old.Age AS 'Old Value',
       New.Age AS 'New Value'
FROM   Table1 Old
       FULL OUTER JOIN Table2 New
           ON Old.Id = New.Id
UNION ALL
SELECT COALESCE(Old.Id,New.Id) AS ID,
       'salary' AS 'Field',
       Old.Salary AS 'Old Value',
       New.Salary AS 'New Value'
FROM   Table1 Old
       FULL OUTER JOIN Table2 New
           ON Old.Id = New.Id

Another way would be to have all differences of each id in a single row:

SELECT COALESCE(Old.Id,New.Id) AS ID,
       Old.Name AS 'OldName',
       New.Name AS 'NewName',
       Old.Age AS 'OldAge',
       New.Age AS 'NewAge',
       Old.Salary AS 'OldSalary',
       New.Salary AS 'NewSalary'
FROM   Table1 Old
       FULL OUTER JOIN Table2 New
           ON Old.Id = New.Id

Upvotes: 0

Valerka
Valerka

Reputation: 11

CREATE TABLE #aaa 
(
    id INT,
    NAME NVARCHAR(10)
)
CREATE TABLE #bbb
(
    id INT,
    NAME NVARCHAR(10)
)
INSERT INTO #aaa( id, NAME )
VALUES  ( 1, N'aaa'), (2, N'bbb')  

INSERT INTO #bbb( id, NAME )
VALUES  ( 1, N'ccc'), (2, N'bbb')


(
    SELECT *
    FROM #aaa
    EXCEPT
    SELECT *
    FROM #bbb            
)
UNION ALL              
(
    SELECT *
    FROM #bbb
    EXCEPT
    SELECT *
    FROM #aaa     
)

If you don't have any relations between this two tables - it's unreal to find "old" and "new" values.

Upvotes: 1

Related Questions