need to find differences between 2 identically structured sql tables

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 wil开发者_运维百科l 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

---

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.

---

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...

---

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

---

I've heard good things about Redgate SQL Data Compare

---

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

---

If you want to avoid listing all the columns to compare and if you want to include and entry for identical rows, you could do the following:

--Data setup
d r o p table tableA;
d r o p table tableB;

create table tableA as (
   select rownum-1 ID, chr(rownum-1+70) bb, chr(rownum-1+100) cc 
      from dual connect by rownum<=4
);

create table tableB as (
   select rownum ID, chr(rownum+70) data1, chr(rownum+100) cc from dual
   UNION ALL
   select rownum+2 ID, chr(rownum+70) data1, chr(rownum+100) cc 
      from dual connect by rownum<=3
);

~

--View Tables.
select * from tableA;
select * from tableB;

~

--Solution.
with UnionedRows As
(
   select * from tableA 
   UNION 
   select * from tableB
)
select ID, sum(MyCount),  
   case 
      when sum(MyCount) = 12 then 'In Table A and Table B - Identical.'
      when sum(MyCount) =  2 then 'In Table A.'
      when sum(MyCount) = 13 then 'In Table A and Table B - Different.'
      when sum(MyCount) = 11 then 'In Table B.'
   end Status
from
(
   select ID, count(*) MyCount from UnionedRows group by ID
   UNION ALL
   select ID, 1 from tableA
   UNION ALL 
   select ID, 10 from tableB
) group by ID order by ID;