Need to find differences between 2 equally structured sql tables
I need to find the differences between 2 identically structured sql tables Each table is loaded from the third patch tool into the sqlserver database.
Table structure:
Issue ID-status-Who
The error id will not be repeated in the table, although it is not explicitly defined as a primary key
There can be additions / deletions / updates between any two tables.
What I need
Number of rows added & their details
Number of rows deleted & their details
Number of rows updates & their details
How to do it
1) it is better to use sql 2) or use datatables
a source to share
You can do this using two left joins and a regular update join. This will show you, in relation to TableA, which rows are added, removed and updated. Note that they can be combined into one 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
A note for the latter, if you need to handle null values, I think you need to customize the where clause.
If the tables are large and this is a permanent operation, you should consider adding indexes to the join columns.
a source to share
if you don't want to copy all the contents of each table in C #, then do it in the database.
All missing TableA or TableB tables will be found here, 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...
a source to share
To find updates , you will need to compare the value of each column with another 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
a source to share
In general, I would recommend a product like Redgate SQL Data Compare , but as one, you can 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
a source to share
If you want to avoid listing all columns for comparison, and if you want to include a record for identical rows, you can 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;
a source to share