> [!NOTE]

> Although a MERGE statement typically requires a staging table in this scenario, the performance of the MERGE statement usually exceeds that of the row-by-row lookup performed by the Lookup transformation. MERGE is also useful when the large size of a lookup table would test the memory that is available to the Lookup transformation for caching its reference table.

Typically, you use the MERGE statement when you want to apply changes that include inserts, updates, and deletions from one table to another table. Prior to [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)], this process required both a Lookup transformation and multiple OLE DB Command transformations. The Lookup transformation performed a row-by-row lookup to determine whether each row was new or changed. The OLE DB Command transformations then performed the necessary INSERT, UPDATE, and DELETE operations. Beginning in [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)], a single MERGE statement can replace both the Lookup transformation and the corresponding OLE DB Command transformations.

The Inventory database has a ProductsInventory table that has ProductID and StockOnHand columns. A Shipments table with ProductID, CustomerID, and Quantity columns tracks shipments of products to customers. The ProductInventory table has to be updated daily based on information in the Shipments table. A single MERGE statement can reduce the inventory in the ProductInventory table based on the shipments made. If the inventory for a product has been reduced to 0, that MERGE statement can also delete that product row from the ProductInventory table.