need to find differences between 2 identically structured sql tables

Question

I need to find differences between 2 identically structured sql tables Each table is being uploaded from a 3rd paty tool into sqlserver database.

The table structure is:

Issue ID-status-Who

Issue ID will not repeated within a table, though its not defined explicitly as primary Key

There could be additions/deletions/Updations between any 2 tables.

What I need

Number of rows added & their details
Number of rows deleted & their details
Number of rows updates & their details

How do I do this

1) is it better to use sql 2) or use datatables

Answer 1

You could do this using two left joins and regular join for the updates. This will show you, with respect to TableA, which rows are added, deleted, and updated. Note these could be contatenated into a single result set.

select b.*, 'added'
from tableb b
   left outer join tablea a on b.IssueID = a.IssueID
where a.IssueID is null

select a.*, 'deleted'
from tablea a
    left outer join tableb b on a.IssueID = b.IssueID
where b.IssueID is null

select a.*, 'updated'
from tablea a
    join tableb b on a.IssueID = b.IssueID
where a.Status <> b.Status or a.Who <> b.Who

Note for the latter, if you need to handle null values, I think you'll need to adjust the where clause.

If the tables are large and this is an ongoing operation, you should consider adding indexes on the join columns.

Answer 2

unless you want to copy the entire contents of each table into C#, do it in the database.

This will find all missing TableA or TableB rows, as well as any changes:

;WITH AllPKs AS
(
SELECT ID FROM TableA
UNION ID FROM TableB
)
SELECT
    z.ID,a.*, b.*
    FROM AllPKs                z
        LEFT OUTER JOIN TableA a ON z.ID=a.ID
        LEFT OUTER JOIN Tableb b ON z.ID=b.ID
    WHERE A.ID IS NULL OR B.ID IS NULL OR a.Col1!=b.Col1 OR a.Col2!=b.Col2 OR...

Answer 3

I've heard good things about Redgate SQL Data Compare

Answer 4

In order to find updates, you will need to compare the value of each column to the other table.

select 'updated',
   a.IssueID as IssueID_A, b.IssueID as IssueID_B
   a.Status as Status_A, b.Status as Status_B,
   a.Who as Who_A, b.Who as Who_B
from tablea a
inner join tableb b 
   on a.IssueID = b.IssueID 
where a.Status <> b.Status or a.Who <> b.Who

Answer 5

In general, I would recommend a product like Redgate SQL Data Compare but as a one off, you could use a script like this:

-- Create some tables and data for testing purposes
USE [tempdb]
SET NOCOUNT ON
GO

DROP TABLE [Issues1]
DROP TABLE [Issues2]
GO

CREATE TABLE [Issues1] ([IssueID] int, [Status] varchar(max), [Who] varchar(max))
CREATE TABLE [Issues2] ([IssueID] int, [Status] varchar(max), [Who] varchar(max))
GO

INSERT [Issues1] VALUES (1, 'aaa', 'bbb')
INSERT [Issues1] VALUES (2, 'ccc', 'ddd')
INSERT [Issues1] VALUES (3, 'eee', 'fff')
GO

INSERT [Issues2] VALUES (1, 'aaa', 'bbb')
INSERT [Issues2] VALUES (3, 'ggg', 'hhh')
INSERT [Issues2] VALUES (4, 'iii', 'iii')
GO

-- **** START OF ANSWER PROPER ****

-- Create some temporary variables to store the change details
DECLARE @Inserts TABLE ([IssueID] int, [Status] varchar(max), [Who] varchar(max))
DECLARE @Updates TABLE ([IssueID] int, [OldStatus] varchar(max), [NewStatus] varchar(max), [OldWho] varchar(max), [NewWho] varchar(max))
DECLARE @Deletes TABLE ([IssueID] int, [Status] varchar(max), [Who] varchar(max))

-- Find all rows that exist in Issues2 but do not exist in Issues1
-- (matching on ID)
INSERT @Inserts
SELECT *
FROM [Issues2]
WHERE [IssueID] NOT IN
(
    SELECT
        [IssueID]
    FROM [Issues1]
)

-- Find all rows existing in both Issues1 and Issues2 (matching on ID)
-- and where either Status or Who has changed
INSERT @Updates
SELECT
    [Issues1].[IssueID],
    [Issues1].[Status],
    [Issues2].[Status],
    [Issues1].[Who],
    [Issues2].[Who]
FROM [Issues1]
    INNER JOIN [Issues2] ON [Issues2].[IssueID] = [Issues1].[IssueID]
        AND
        (
            [Issues2].[Status] != [Issues1].[Status]
                OR [Issues2].[Who] != [Issues1].[Who]
        )

-- Find all rows that exist in Issues1 but do not exist in Issues2
-- (matching on ID)
INSERT @Deletes
SELECT *
FROM [Issues1]
WHERE [IssueID] NOT IN
(
    SELECT
        [IssueID]
    FROM [Issues2]
)

-- Output the results
SELECT
    (SELECT COUNT(*) FROM @Inserts) AS [Number Inserted],
    (SELECT COUNT(*) FROM @Updates) AS [Number Updated],
    (SELECT COUNT(*) FROM @Deletes) AS [Number Deleted]

SELECT 'INSERTED', * FROM @Inserts
SELECT 'UPDATED', * FROM @Updates
SELECT 'DELETED', * FROM @Deletes
GO