Working With Merge and Identity column – A Practical Scenario

Introduction

As we all know about the Identity columns and Merge statement. We are not going to discuss any boring theoretical tropics related to it. Better we are discussing here with a practical scenario of merging records. Hope all of you must enjoy it and it will be informative.

The Scenario

We have a Table with Identity Columns named #tbl_TempStudentRecords. The table details are mentioned bellow.

Column Name

SrlNo

StudentName

StudentClass

StudentSection

We have another table named #tbl_TempStudrntMsrks. The table details are mentioned bellow.

Column Name

SrlNo

StubjectName

MarksObtain

What we want to do is, we have another set of table called tbl_StudentDetails mentioned bellow.

Column Name

StdRoll  (PK)

StudentName

StudentClass

StudentSection

Another table named tbl_StudentMarks

Column Name

IdNo  (PK)

StdRoll  (FK) References  [tbl_StudentDetails].[StdRoll]

SubjectName

MarksObtain

Her we can insert records very easily in tbl_StudentDetails from #tbl_TempStudentRecords very easily. But the main problem is the IDENTITY columns in the Table named [tbl_StudentDetails].[ StdRoll].

When we insert records the Identity columns values generate automatically.

When we are trying to insert records into the table named tbl_StudentMarks from Table named #tbl_TempStudrntMsrks we have to provide the StdRoll values, which is the Foreign Key References to the Table named [tbl_StudentDetails].[ StdRoll].

Think one minute with the case scenario. Hope you can understand the problem. Now we have to solve it and we are not using any LOOP for that and NOT even any DDL operation to change the structure of base table. We are just using the SET BASED operation to make performance high.

How to Solve it

Step – 1 [ Create the Base Table First ]

IF OBJECT_ID('tempdb..#tbl_TempStudentRecords')IS NOT NULL

   BEGIN

       DROP TABLE #tbl_TempStudentRecords;

   END

GO

CREATE TABLE #tbl_TempStudentRecords

   (

    SrlNo         BIGINT           NOT NULL,

    StudentName   VARCHAR(50)      NOT NULL,

    StudentClass  INT              NOT NULL,

    StudentSection CHAR(1)          NOT NULL

   );

GO

IF OBJECT_ID('tempdb..#tbl_TempStudrntMsrks')IS NOT NULL

   BEGIN

       DROP TABLE #tbl_TempStudrntMsrks;

   END

GO

CREATE TABLE #tbl_TempStudrntMsrks

    (

        SrlNo           BIGINT      NOT NULL,

        StubjectName    VARCHAR(50) NOT NULL,

        MarksObtain     INT         NOT NULL

       );

GO

IF OBJECT_ID(N'[dbo].[tbl_StudentDetails]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_StudentDetails];

   END     

GO

CREATE TABLE [dbo].[tbl_StudentDetails]

  (

    StdRoll       BIGINT     NOT NULL IDENTITY(100,1) PRIMARY KEY,

    StudentName   VARCHAR(50) NOT NULL,

    StudentClass  INT        NOT NULL,

    StudentSection CHAR(1)    NOT NULL

  );

GO

IF OBJECT_ID(N'[dbo].[tbl_StudentMarks]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_StudentMarks];

   END     

GO

CREATE TABLE [dbo].[tbl_StudentMarks]

  (

    IdNo          BIGINT      NOT NULL IDENTITY(1,1) PRIMARY KEY,

    StdRoll       BIGINT      NOT NULL, 

    SubjectName   VARCHAR(50) NOT NULL,

    MarksObtain   INT         NOT NULL

  );

GO

ALTER TABLE [dbo].[tbl_StudentMarks]

ADD CONSTRAINT FK_StdRoll_tbl_StudentMarks

FOREIGN KEY(StdRoll)

REFERENCES [dbo].[tbl_StudentDetails](StdRoll);

Step – 2 [ Inserting Records in Temp Table ]

INSERT INTO #tbl_TempStudentRecords

  (SrlNo, StudentName, StudentClass, StudentSection)

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

      (2, 'Preeti Sharma', 1, 'A'),

      (3, 'Deepasree Das', 1, 'A');

INSERT INTO #tbl_TempStudrntMsrks

   (SrlNo, StubjectName, MarksObtain)

VALUES  (1, 'Bengali', 50),

        (1, 'English', 70),

        (1, 'Math', 80),

        (2, 'Bengali', 0),

        (2, 'English', 70),

        (2, 'Math', 80),

        (3, 'Bengali', 20),

        (3, 'English', 90),

        (3, 'Math', 95);

Step – 3 [ Now Solve it By MERGE Statement ]

BEGIN

      DECLARE @MappingTable TABLE

       ([NewRecordID] BIGINT,

        [OldRecordID] BIGINT)

      MERGE [dbo].[tbl_StudentDetails] AS target

      USING (SELECT [SrlNo] AS RecordID_Original

                 ,[StudentName]

                 ,[StudentClass]

                         ,[StudentSection]

               FROM  #tbl_TempStudentRecords

           ) AS source

      ON (target.StdRoll = NULL)

      WHEN NOT MATCHED THEN

      INSERT ([StudentName], [StudentClass], [StudentSection])

      VALUES (source.[StudentName],source.[StudentClass],  

              source.[StudentSection])

      OUTPUT inserted.[StdRoll], source.[RecordID_Original]

      INTO   @MappingTable;

      --- Now Map table is ready and we can use it ---

      INSERT INTO [dbo].[tbl_StudentMarks]

          (StdRoll, SubjectName, MarksObtain)

      SELECT b.NewRecordID, a.StubjectName, a.MarksObtain

      FROM   #tbl_TempStudrntMsrks AS a

             INNER JOIN @MappingTable AS b

                  ON a.SrlNo = b.OldRecordID;

END

GO

Step – 2 [ Observation ]

SELECT * FROM [dbo].[tbl_StudentDetails];

GO

SELECT * FROM [dbo].[tbl_StudentMarks];

GO

StdRoll                 StudentName                   StudentClass     StudentSection

100                         Joydeep Das                      1                              A

101                         Preeti Sharma                    1                              A

102                         Deepasree Das                   1                              A

IdNo      StdRoll                 SubjectName                         MarksObtain

1              100                         Bengali                                 50

2              100                         English                                  70

3              100                         Math                                      80

4              101                         Bengali                                  0

5              101                         English                                  70

6              101                         Math                                      80

7              102                         Bengali                                  20

8              102                         English                                  90

9              102                         Math                                      95

Hope you like it.

Posted by: MR. Joydeep Das