Skip to main content

Merge Statement Part-2

With Merge statement  we can merge the data from Souce table into Target table.

Synatx:

MERGE INTO <Target Table > AS TRG
USING <Souce Table> As SRC
ON <Merge Conidtion>
WHEN MATCHED [AND Condition]
THEN <Action>
WHEN NOT MATCHED [BY TARGET ] [AND Condition]
THEN <Action>
WHEN NOT MATCHED BY SOURCE [AND CONIDTION]
THEN <Action.

MERGE INTO <Target Table>: This cluase define the  Target table for the  operation.

USING <Source Table>:This clause define the Source table for the opearation. In Source Table we can use Table ,CTE,Dervied Table,Some Other Database Table,OPENROWSET, XQUERY.

ON<Merge Condition>: This is just like as ON Clause like in Joins. This Statement defines the Both Souce table and Target table Matched or NotMatched.

WHEN MATCHED [AND Condition]  THEN <ACTION>: This clause defines the When Both Souce Table and Target Table Matched based on key .Here [AND Condition] is optional . Here We can perform two Actions Either Update or Delete on target table.

WHEN NOT MATCHED [BY TARGET ] [AND Condition] THEN <ACTION>: This clause defines the When Target Table Matched based on key .Here [AND Condition] is optional . Here We can perform Only one ..Actions i,e. Insert.

WHEN NOT MATCHED BY SOURCE [AND Condition] THEN <ACTION>: This clause defines the When Source Table Matched based on key .Here [AND Condition] is optional .  Here We can perform two Actions Either Update or Delete on target table.

Real Tme Scenario Using Merge Statement
--Create Student Table
if object_id('Student') is null
create table Student(id int identity(1,1) ,Name varchar(20), Marks int)

--Created Stored Procedure With Old Way
create procedure InsUpStudent
(@Name varchar(20),@Marks int)
as
begin
   if exists(select * from Student where Name=@Name)
                 begin
                                update Student set Marks=@Marks where Name=@Name
                 end
   else
                begin
                                insert into Student(Name,Marks) values(@Name,@Marks)
                end
end



--Test Some Sample data with above procedure.
exec InsUpStudent 'Rakesh',500 --Here record need to insert into Student table beacuse of Rakesh does not exists.
exec InsUpStudent 'Rakesh',600 --Here record need to update into Student table beacuse of Rakesh already exists.


Using Merege Statement:

truncate table Student
drop procedure InsUpStudent

--Create Stored Procedure With Merge Statement
create procedure InsUpStudent
(@Name varchar(20),@Marks int)
as
begin
                merge into Student as trg
                using (values(@Name,@Marks)) as src(Name,Marks)
                on trg.Name=Src.Name and (trg.Name<> src.Name or trg.Marks <>src.Marks)
                when matched then update
                set trg.Name=src.Name, trg.Marks=src.Marks
                when not matched then insert (Name,Marks)
                values(src.Name,src.Marks);
end



--Test Some Sample data with above procedure.
exec InsUpStudent 'Rakesh',500 --Here record need to insert into Student table beacuse of Rakesh does not exists.
exec InsUpStudent 'Rakesh',600 --Here record need to update into Student table beacuse of Rakesh already exists.


Note : WHEN MATCHED [AND Condition]  THEN <ACTION>, WHEN NOT MATCHED [BY TARGET ] [AND Condition] THEN <ACTION> AND WHEN NOT MATCHED BY SOURCE [AND Condition] THEN <ACTION> All these clauses not mandatory only any of the one is required.
Labels:

Comments

Post a Comment

Popular posts from this blog

SET ROWCOUNT

Stop the processing after the number of rows has been affected. Example: Step-1: Create the Table and insert some sample data. CREATE TABLE STUDENT ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , STUDENTNAME VARCHAR ( 100 ) NOT NULL , LIVINGLOCATION VARCHAR ( 100 ) NULL ) INSERT INTO STUDENT ( STUDENTNAME , LIVINGLOCATION ) SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' In above Insert Statement we inserted 5 sample records. Step-2: Using SET ROWCOUT SET ROWCOUNT 3 ; SELECT * FROM STUDENT ORDER BY ID DESC It will affected 3 rows after that processing has been stopped. Step-2: Using SET ROWCOUT 0 SET ROWCOUNT 0 ; SELECT * FROM STUDENT ORDER BY ID DESC ...
7 comments
Read more

Simple Trick Using ISNULL

Simple Trick Using ISNULL: Step-1: Create Sample Student Table. CREATE TABLE Student ( Id INT IDENTITY ( 1 , 1 ) PRIMARY KEY , StudentName VARCHAR ( 100 ) NOT NULL, LivingLocation VARCHAR ( 100 ) NULL ) Step-2 :Insert Some Sample Data INSERT INTO Student SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' Step-3 : Select Data from Student Table SELECT * FROM Student If we observe above data of student table we have student id 4 record LivingLocation is NULL. Step -4 :Select Data from Student Table With Where Clause. SELECT * FROM Student WHERE LivingLocation = 'Hyderabad' In the above query we are included the where clause w...
3 comments
Read more

Coalesce function

Coalesce function returns the first non-null value among the arguments. Syntax: Coalesce (expression [,..n]) Here is example using Coalesce function Example 1 DECLARE @Str1 varchar ( 10 ), @str2 varchar ( 20 ), @Str3 varchar ( 20 ) SET @Str2 = 'Sql' , @Str3 = 'Server' SELECT COALESCE ( @Str1 , @str2 , @Str3 ) As [Coalesce] In above example @Str2 value is ‘Sql’ , @str3 value is ‘Server’  and @str1 values is Null because it not assigned any value . Output: It return’s “Sql” because Coalesce function return’s first non null value. Example 2: Coalesce in select statement. IF OBJECT_ID ( 'Employee' , 'U' ) IS NOT NULL DROP TABLE Employee CREATE TABLE Employee (   ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY ,   NAME VARCHAR ( 20 ),   SALARY INT ) INSERT INTO Employee   ( NAME , SALARY ) VALUES ( 'Rakesh' , 5000 ),(NULL, 6000 ),( 'Naresh...
1 comment
Read more