Friday 11 March 2016

Trying to Compare Between Two Table

Introduction

We are trying to compare between two tables.  There is lot of method to compare but we are trying to use a simple one. Hope it will be interesting.

How we Compare ?

Step – 1  [ Create the Base Table  ]

CREATE TABLE TEST
(
   ID           INT,
   NAME   CHAR(1));

GO

CREATE TABLE TEST1
(
   ID           INT,
   NAME   CHAR(1));
GO

Step – 2 [ Insert some records in it ]

INSERT INTO TEST VALUES(1, 'A'),(2,'B');

INSERT INTO TEST1 VALUES(1, 'A'),(2,'B');

Step – 3 [ Now compare – But both table have same records ]

SELECT MIN(a.TableName) AS TableName, a.ID, a.NAME
FROM (
SELECT 'TEST' AS TableName, ID, NAME FROM TEST
UNION ALL
SELECT 'TEST1' AS TableName,  ID, NAME FROM TEST1
)a
GROUP BY a.ID, a.NAME
HAVING COUNT(*) = 1;

Output:
Nothing.


Step – 4 [ Now Change some records in both Table ]

INSERT INTO TEST1 VALUES(1, 'A'),(2,'B');
INSERT INTO TEST1 VALUES(4, 'D');

Step – 5 [ Now Compare it Again ]

SELECT MIN(a.TableName) AS TableName, a.ID, a.NAME
FROM (
SELECT 'TEST' AS TableName, ID, NAME FROM TEST
UNION ALL
SELECT 'TEST1' AS TableName,  ID, NAME FROM TEST1
)a
GROUP BY a.ID, a.NAME
HAVING COUNT(*) = 1;

Output:

TableName ID NAME
TEST1 D
TEST 3 X




Hope you like it.




Posted By: MR. JOYDEEP DAS

3 comments:

  1. Next step is to Implement it in SSIS

    ReplyDelete
  2. Is it related to data comparison?

    ReplyDelete