Home > SQL SERVER > Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE(Sql server 2008)

Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE(Sql server 2008)

Hi folks,

Today i came across with very interesting situation where i wanted to Execute Insert,Update and Delete statement as per some specified conditions.

Actually i was going to use the usual way to do it by using

If Exists clause

For ex.

If Exists(select 1 from tblClass where sample_ID=1)

Begin

//Do insert update whatever your logic.

End

 

But My colleague tell me about the MERGED STATEMENT .I searched it and i can tell you that its a best Statement for conditional insert,update,delete,

You have this feature only in SQL Server 2008

 

Syntax of MERGE statement is as following:
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,…n ] ) ]
;

 

For Example :-

we have a table tblClass  like:-

image

 

Now Our condition is like we want to Update the class name “Fifth”

to “Seventh” if it exists

and if we don’t have such a record for “Fifth” we will insert the record for “Seventh” standard.

 

Merge tblclass as stm
using (select class_Id from tblclass where class_Name=’Fifth’) sd 
on stm.class_id=sd.class_id
when matched then update set class_name=’Seventh’
when not matched then Insert (Class_Name,IsActive)Values(‘Seventh’,1);

 

Thank You.

Advertisements
Categories: SQL SERVER
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: