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

PIVOT Made Easy

Introduction

Most of the developers made mistake in creation of PIVOT query as the syntax is little bit hazy. Here we are trying to make a Stored Procedure which can dynamically create Pivot Query by passing the parameters value only. Hope it will be informative and easy to implement.

Create Base Table and Make PIVOT in Regular Faison

Step-1 [ The Base Table ]

CREATE TABLE dbo.Products

(

  ProductID INT PRIMARY KEY,

  Name      NVARCHAR(255) NOT NULL UNIQUE

);

GO

INSERT dbo.Products VALUES

(1, N'foo'),

(2, N'bar'),

(3, N'kin');

GO

CREATE TABLE dbo.OrderDetails

(

  OrderID INT,

  ProductID INT NOT NULL

    FOREIGN KEY REFERENCES dbo.Products(ProductID),

  Quantity INT

);

GO

INSERT dbo.OrderDetails VALUES

(1, 1, 1),

(1, 2, 2),

(2, 1, 1),

(3, 3, 1);

Step-2 [ Make PIVOT ]

SELECT p.[foo], p.[bar], p.[kin]

FROM

(

  SELECT p.Name, o.Quantity

   FROM dbo.Products AS p

   INNER JOIN dbo.OrderDetails AS o

          ON p.ProductID = o.ProductID

) AS j

PIVOT

(

  SUM(Quantity) FOR Name IN ([foo],[bar],[kin])

) AS p;

Output:

foo      bar      kin

2          2          1

Now Make the Dynamic Stored Procedure and Execute it

Step – 1 [ Create the Stored Procedure ]

IF OBJECT_ID(N'[dbo].[proc_Pivot]', N'P')IS NOT NULL

   DROP PROCEDURE [dbo].[proc_Pivot];

GO

CREATE PROCEDURE [dbo].[proc_Pivot]

       (

            @p_TableName        VARCHAR(Max),

            @p_TableCol         VARCHAR(Max),

            @p_Aggrigate        CHAR(10),

            @p_AggrigateCol     VARCHAR(50),

            @p_PivtCompCol      VARCHAR(50),

            @p_PivotColumns     VARCHAR(Max)

       )

AS

BEGIN

DECLARE @v_SQL               VARCHAR(Max),

        @v_PivotColumns      VARCHAR(Max),

        @v_PivotColumnsall   VARCHAR(Max);

SET @v_PivotColumns = 'pvt.[' + REPLACE(@p_PivotColumns, ',', '],pvt.[') + ']';

SET @v_PivotColumnsall = '[' + REPLACE(@p_PivotColumns, ',', '],[') + ']';

SET @v_SQL = 'SELECT ' + @v_PivotColumns + '

                FROM  (SELECT * FROM '+ @p_TableName + ') AS j

                PIVOT

                ( ' + @p_Aggrigate +'('+ @p_AggrigateCol +') FOR '+

                           @p_PivtCompCol + ' IN ( '+ @v_PivotColumnsall +') ) AS pvt';

EXEC(@v_SQL);

END

Step-2 [ Execute The Stored Procedure ]

EXEC [dbo].[proc_Pivot]

            @p_TableName        = 'view_ProductOrder',

            @p_TableCol         = 'Name,Quantity',

            @p_Aggrigate        = 'SUM',

            @p_AggrigateCol     = 'Quantity',

            @p_PivtCompCol      = 'Name',

            @p_PivotColumns     = 'foo,bar,kin';

Output:

foo      bar      kin

2          2          1

Hope you like it.

Posted by: MR. JOYDEEP DAS