MERGE is a new feature that provides an
efficient way to perform multiple DML operations. In previous versions
of SQL Server, we had to write separate statements to INSERT, UPDATE, or
DELETE data based on certain conditions, but now, using MERGE statement
we can include the logic of such data modifications in one statement
that even checks when the data is matched then just update it and when
unmatched then insert it.
One of the most important advantage of MERGE statement is all the data is read and processed only once.There is no maximum number limitation on merge statements in a stored procedure
Example:
Let’s create Student Details and StudentTotalMarks and inserted some records.
This can improve query performance because the operations are performed
within a single statement, therefore, minimizing the number of times the
data in the source and target tables are processed. However,
performance gains depend on having correct indexes, joins, and other
considerations in place.
Student Details:
CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO
INSERT INTO StudentDetails
VALUES(1,'SMITH')
INSERT INTO StudentDetails
VALUES(2,'ALLEN')
INSERT INTO StudentDetails
VALUES(3,'JONES')
INSERT INTO StudentDetails
VALUES(4,'MARTIN')
INSERT INTO StudentDetails
VALUES(5,'JAMES')
GO
StudentTotalMarks:
CREATE TABLE StudentTotalMarks
(
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
INSERT INTO StudentTotalMarks
VALUES(3,200)
GO
MERGE
StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
No comments:
Post a Comment