Execute the following
Microsoft SQL Server T-SQL scripts in SSMS Query Editor to demonstrate UPSERT, update or insert, operation using the MERGE statement. If the row exists as identified by the 3 key columns, UPDATE is performed on OrderQuantity and SalesAmount, otherwise INSERT action is carried out. MERGE can do INSERT, UPDATE and DELETE in a single statement.
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
Related article:
MERGE (Transact-SQL)
|
