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 – 4 [ 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
June 28, 2015
by Joydeep Das
·
5,147 Views