MERGE statement

----------MERGE STATEMENT--------------

--if there is an IDENTITY no need to pass ID value

MERGE C AS T USING B AS S ON T.ID=S.ID AND T.NAME=S.NAME
WHEN MATCHED THEN UPDATE SET T.NAME=S.NAME
WHEN NOT MATCHED THEN INSERT VALUES(NAME) OUTPUT inserted.*,deleted.*,$ACTION;

--C target table


-- Update existing, add missing
MERGE INTO dbo.tbl_Customers AS C
USING dbo.tbl_CustomersTemp AS CT
        ON C.CustID = CT.CustID
WHEN MATCHED THEN
    UPDATE SET
      C.CompanyName = CT.CompanyName,
      C.Phone = CT.Phone
WHEN NOT MATCHED THEN 
      INSERT (CustID, CompanyName, Phone)

      VALUES (CT.CustID, CT.CompanyName, CT.Phone);

TRIGGERs

----------------------AFTER TRIGGER----------------

--create TABLE & INSERT records in it

CREATE TABLE Employee_Demo
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Sal decimal (10,2)
)
-- Now Insert records 
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);
Insert into Employee_Demo values ('Avin',1100);
Insert into Employee_Demo values ('Manoj',1300);

Insert into Employee_Demo values ('Riyaz',1400);

--create another TABLE for insert TRIGGER executions  
CREATE TABLE Employee_Demo_Audit
(
 Emp_ID int,
 Emp_Name varchar(55),
 Emp_Sal decimal(10,2),
 Audit_Action varchar(100),
 Audit_Timestamp datetime

)

--create  TRIGGER for executions

CREATE TRIGGER trgAfterInsert on Employee_Demo
FOR INSERT
AS 
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.'; 
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'

--Now Insert record
insert into Employee_Demo(Emp_Name,Emp_Sal)values ('suresh',1000);

--select statements

select * from Employee_Demo

select * from Employee_Demo_Audit