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 4  D
TEST 3 X




Hope you like it.




Posted By: MR. JOYDEEP DAS