DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the MERGE statement instead of UPDATE?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to demonstrate how to use the MERGE statement instead of INSERT.

 

USE tempdb;

GO

 

SELECT TOP (5000) ResellerKey,

                  OrderDateKey,

                  ProductKey,

                  OrderQuantity,

                  SalesAmount

INTO   FactResellerSales

FROM   AdventureWorksDW2008.dbo.FactResellerSales

GO

-- (5000 row(s) affected)

 

SELECT TOP (8000) ResellerKey,

                  OrderDateKey,

                  ProductKey,

                  OrderQuantity,

                  SalesAmount

INTO   ResellerSalesTransaction

FROM   AdventureWorksDW2008.dbo.FactResellerSales

GO

-- (8000 row(s) affected)

 

DELETE rsc

FROM   ResellerSalesTransaction rsc

       JOIN (SELECT TOP 1000 *

             FROM   ResellerSalesTransaction

             ORDER  BY ResellerKey DESC) x

         ON x.ResellerKey = rsc.ResellerKey

GO

--(1010 row(s) affected)

 

UPDATE TOP (6000) ResellerSalesTransaction

SET    SalesAmount = SalesAmount * 1.1

GO

 

SELECT TOP (10) *

FROM   FactResellerSales

ORDER  BY ResellerKey,

          OrderDateKey,

          ProductKey

GO

-- (6000 row(s) affected)

 

SELECT BeforeMatchingFactCount=COUNT(*)

FROM   FactResellerSales

WHERE ResellerKey IN (SELECT ResellerKey FROM ResellerSalesTransaction)

GO

-- 4332

 

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

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

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

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;

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

GO

-- 4332 row(s) affected)

 

SELECT TOP (10) *

FROM   FactResellerSales

ORDER  BY ResellerKey,

          OrderDateKey,

          ProductKey

GO

 

SELECT AfterFactCount=COUNT(*)

FROM   FactResellerSales

GO

-- 5000

 

-- Cleanup

USE tempdb;

DROP TABLE ResellerSalesTransaction

GO

 

DROP TABLE FactResellerSales

GO 

Related article:

MERGE (Transact-SQL)

Hugo Kornelis: Let's deprecate UPDATE FROM!

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE