How to Merge Tables ?

/*MERGE STATEMENT WITH EXAMPLE
•    The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
•    The USING clause specifies the data source being joined with the target.
•    The ON clause specifies the join conditions that determine where the target and source match.
•    The WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the
    ON clause and any additional search criteria specified in the WHEN clauses.
•    The OUTPUT clause returns a row for each row in the target that is inserted, updated, or deleted.
*/
/*==================================================================
Target table
==================================================================*/
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10),
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
    
/*==================================================================
Source table
==================================================================    */
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10),
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO

INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');
GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO

--Query Using MERGE:
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

use tempdb;               

select top (5000) ResellerKey, OrderDateKey, ProductKey, OrderQuantity, SalesAmount

into FactResellerSales

from AdventureWorksDW2008.dbo.FactResellerSales

go 

select top (8000) ResellerKey, OrderDateKey, ProductKey, OrderQuantity, SalesAmount

into ResellerSalesTransaction

from AdventureWorksDW2008.dbo.FactResellerSales

go

             

delete rsc

from  ResellerSalesTransaction rsc

join (select top 1000 * from ResellerSalesTransaction order by ResellerKey desc) x

on x.ResellerKey=rsc.ResellerKey

go

update top (6000) ResellerSalesTransaction

set SalesAmount = SalesAmount * 1.1

go             

select top (10) * from FactResellerSales

order by ResellerKey, OrderDateKey, ProductKey

go 

select BeforeFactCount=COUNT(*) from FactResellerSales

go 

-------------------------------------------------------------------- 

-- Test data sets created, ready for the MERGE (update or insert)

-------------------------------------------------------------------- 

MERGE FactResellerSales AS fact

USING (

  SELECT *  FROM ResellerSalesTransaction

) AS feed

ON (    fact.ProductKey = feed.ProductKey

    AND fact.ResellerKey = feed.ResellerKey

    AND fact.OrderDateKey = feed.OrderDateKey )

WHEN MATCHED THEN

    UPDATE SET

        fact.OrderQuantity = fact.OrderQuantity + feed.OrderQuantity

        ,fact.SalesAmount = fact.SalesAmount + feed.SalesAmount 

WHEN NOT MATCHED THEN

    INSERT (ResellerKey, OrderDateKey, ProductKey, OrderQuantity, SalesAmount)

    VALUES (feed.ResellerKey, feed.OrderDateKey, feed.ProductKey,

            feed.OrderQuantity, feed.SalesAmount);

--------------------------------------------------------------------  

go 

select top (10) * from FactResellerSales

order by ResellerKey, OrderDateKey, ProductKey

go 

select AfterFactCount=COUNT(*) from FactResellerSales

go 

-- Cleanup

use tempdb;

drop table ResellerSalesTransaction

go

drop table FactResellerSales

go