Comparison between Two Table Object

Introduction

Sometime developer need to compare the data between two table whether they are exactly matched or not. We developer are used number of methodology for that.
Here in this article we are trying to discuss about a simple methodology to compare between two tables. Hope you find it informative.

Let’s take an example to find it

Step-1 [ Creating Base Table ]

CREATE TABLE tbl_A

    (

         StdRoll        INT,

         StdName        VARCHAR(50),

         StdClass       INT

      );

GO

CREATE TABLE tbl_B

    (

         StdRoll        INT,

         StdName        VARCHAR(50),

         StdClass       INT

      );

GO

Step-2 [ Inserting Records in Table Object ]

INSERT INTO tbl_A

       (StdRoll, StdName, StdClass)

VALUES (1, 'Joydeep Das', 1),

       (2, 'Arabind Sarkar', 1),

         (3, 'Santinath Mondal', 1);

GO

INSERT INTO tbl_B

       (StdRoll, StdName, StdClass)

VALUES (1, 'Joydeep Das', 1),

       (2, 'Arabind Sarkar', 1),

         (3, 'Santinath Mondal', 1);

GO

Step -4 [ Now Compare Both Table Object ]

SELECT BINARY_CHECKSUM(StdRoll, StdName, StdClass) AS [Checksum]
FROM   tbl_A;

GO

SELECT BINARY_CHECKSUM(StdRoll, StdName, StdClass) AS [Checksum]
FROM   tbl_B;

GO

Checksum

-----------

841019331

321100711

850895550

(3 row(s) affected)

Checksum

-----------

841019331

321100711

850895550

(3 row(s) affected)

Step -5 [ Now update some Records and Compare it Again ]

UPDATE tbl_B SET StdName = 'Radha Ranjan' WHERE StdRoll = 1;

GO

SELECT BINARY_CHECKSUM(StdRoll, StdName, StdClass) AS [Checksum]
FROM   tbl_A;

GO

SELECT BINARY_CHECKSUM(StdRoll, StdName, StdClass) AS [Checksum]
FROM   tbl_B;

GO

Checksum

-----------

841019331

321100711

850895550

(3 row(s) affected)

Checksum

-----------

1412215810

321100711

850895550

(3 row(s) affected)

If we look at the output the First record checksum value of both the table is not matched.

Hope you like it.

Posted By: MR. JOYDEEP DAS