Merge Statement Part-1

 --Create Source_Student

 if object_id('Source_Student') is null

 create table Source_Student(id int identity(1,1) ,Name varchar(20)Marks int)

               

--Insert Some Sample Data to Source_Student

  insert Source_Student (Name,Marks) values('Rakesh',500)

  insert Source_Student (Name,Marks) values('Raju',400)

               

--Create Target_Student

 if object_id('Target_Student') is null

 create table Target_Student(id int ,Name varchar(20), Marks int)

               

--Insert Some Sample Data to Target_Student

  insert Target_Student (id,Name,Marks) values(1,'Rakesh',600)

  insert Target_Student (id,Name,Marks) values(3,'Nani',700)

               

  --Select Two Tables Data

   select * from Source_Student

   select * from Target_Student

In the Source table Id -1 record need to update marks column. Id-2 record need to insert in the Target Column. In the Target column Id-3 record need delete from Target column.

  --Run Merge Satement
  merge into Target_Student as trg

   using  Source_Student  as src 

   on trg.Name=Src.Name

    when matched then update

              set trg.Name=src.Name, trg.Marks=src.Marks

    when not matched then insert (id,Name,Marks)

                                    values(src.id,src.Name,src.Marks)

   when not matched by source then delete;

Select * from Target_Student