Friday, June 17, 2011

What is Merge Operation in SqlServer 2008?

Many Times while writing Sql Statement we use IF ELSE Statement ,IF EXISTS Statement & IF NOT EXISTS Statement and on the basis of found result we update ,Delete Or Insert the DataBase table.In that case we have to write number of lines.
In SqlServer 2008 Microsoft has provided MERGE statement which is not only simple but also have high performance.
The MERGE statement performs INSERT/UPDATE/DELETE operations on a target table based on the results of a join with a source table.
e.g. A School is conducting a Test for taking admission in new Batch and after Entrance Examination School Authority has defined some rule for rejection/selection of students .
Rules.
1. Delete All Records From The Student Table Where Marks < 30

2. Insert All the Records in The Student Table where Marks >=30 if not exists in the Student Table
3. Update the Result column of Student Table as Pass if Marks >30
Table ScreenShot
--Try to Implement the logic for the same as you are doing till now in sqlserver 2005 and than compare the effort and performance from below code using Merge Statement.
Using Merge Statement:


After Execution of the below Merge statement final output looks like as below.

No comments: