在 Integration Services 包中执行 MERGEMERGE in Integration Services Packages

适用对象:是SQL Server,包含 Linux 版 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL ServerIntegration ServicesIntegration Services的当前版本中,执行 SQL 任务中的 SQL 语句可以包含 MERGE 语句。In the current release of SQL ServerSQL ServerIntegration ServicesIntegration Services, the SQL statement in an Execute SQL task can contain a MERGE statement. 使用此 MERGE 语句可以在一个语句中完成多个 INSERT、UPDATE 和 DELETE 操作。This MERGE statement enables you to accomplish multiple INSERT, UPDATE, and DELETE operations in a single statement.

若要在包中使用 MERGE 语句,请执行下列步骤:To use the MERGE statement in a package, follow these steps:

  • 创建用于将源数据加载、转换和保存到临时表的数据流任务。Create a Data Flow task that loads, transforms, and saves the source data to a temporary or staging table.

  • 创建包含 MERGE 语句的执行 SQL 任务。Create an Execute SQL task that contains the MERGE statement.

  • 将数据流任务连接到执行 SQL 任务,并将临时表中的数据用作 MERGE 语句的输入。Connect the Data Flow task to the Execute SQL task, and use the data in the staging table as the input for the MERGE statement.

    备注

    尽管在此方案中,MERGE 语句通常需要临时表,但 MERGE 语句的性能通常优于由查找转换执行的逐行查找的性能。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 也非常有用。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.

有关支持使用 MERGE 语句的示例目标组件,请参阅 CodePlex 社区示例 MERGE Destination(MERGE 目标)。For a sample destination component that supports the use of the MERGE statement, see the CodePlex community sample, MERGE Destination.

使用 MERGEUsing MERGE

通常,当需要应用包括从一个表到另一个表的插入、更新和删除等更改时,可使用 MERGE 语句。Typically, you use the MERGE statement when you want to apply changes that include inserts, updates, and deletions from one table to another table. SQL Server 2008SQL Server 2008之前,此过程需要一个查找转换和多个 OLE DB 命令转换。Prior to SQL Server 2008SQL Server 2008, 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. OLE DB 命令转换然后会执行必要的 INSERT、UPDATE 和 DELETE 操作。The OLE DB Command transformations then performed the necessary INSERT, UPDATE, and DELETE operations. SQL Server 2008SQL Server 2008开始,一个 MERGE 语句即可替代查找转换和相应的 OLE DB 命令转换。Beginning in SQL Server 2008SQL Server 2008, a single MERGE statement can replace both the Lookup transformation and the corresponding OLE DB Command transformations.

用于增量加载的 MERGEMERGE with Incremental Loads

变更数据捕获功能是 SQL Server 2008SQL Server 2008 中的新增功能,使用该功能可以方便可靠地对数据仓库执行增量加载。The change data capture functionality that is new in SQL Server 2008SQL Server 2008 makes it easier to perform incremental loads reliably to a data warehouse. 除了使用参数化的 OLE DB 命令转换来执行插入和更新之外,还可以使用 MERGE 语句来合并这两项操作。As an alternative to using parameterized OLE DB Command transformations to perform the inserts and the updates, you can use the MERGE statement to combine both operations.

有关详细信息,请参阅 将变更应用到目标For more information, see Apply the Changes to the Destination.

在其他情况下的 MERGEMERGE in Other Scenarios

在以下情况下,可以在 Integration ServicesIntegration Services 包的外部或内部使用 MERGE 语句。In the following scenarios, you can use the MERGE statement either outside or inside an Integration ServicesIntegration Services package. 不过,从多个异类源加载此数据以及随后合并和清除该数据通常都需要 Integration ServicesIntegration Services 包。However, an Integration ServicesIntegration Services package is often required to load this data from multiple heterogeneous sources, and then to combine and cleanse the data. 因此,为了使用方便和便于维护,可以考虑在包中使用 MERGE 语句。Therefore, you might consider using the MERGE statement in a package for convenience and ease of maintenance.

跟踪购买习惯Track Buying Habits

数据仓库中的 FactBuyingHabits 表会跟踪客户购买指定产品的最后日期。The FactBuyingHabits table in the data warehouse tracks the last date on which a customer bought a given product. 该表由 ProductID、CustomerID 和 PurchaseDate 列组成。The table consists of ProductID, CustomerID and PurchaseDate columns. 事务性数据库每周都会生成一个包括该周采购情况的 PurchaseRecords 表。Every week, the transactional database generates a PurchaseRecords table that includes the purchases made during that week. 目标是使用一个 MERGE 语句将 PurchaseRecords 表中的信息合并到 FactBuyingHabits 表中。The objective is to use a single MERGE statement to merge the information in the PurchaseRecords table into the FactBuyingHabits table. 对于不存在的产品-客户对,MERGE 语句将插入新行。For product-customer pairs that do not exist, the MERGE statement inserts new rows. 对于已存在的产品-客户对,MERGE 语句会更新最近的购买日期。For product-customer pairs that exist, the MERGE statement updates the most recent date-of-purchase.

跟踪价格历史记录Track Price History

DimBook 表表示某书商库存中的图书列表,并标识每本书的价格历史记录。The DimBook table represents the list of books in the inventory of a book seller and identifies the price history of each book. 此表包含以下列:ISBN、ProductID、Price、Shelf 和 IsCurrent。This table has these columns: ISBN, ProductID, Price, Shelf, and IsCurrent. 此表还将书的每个价格显示为一行。This table also has one row for each price the book has had. 其中一行显示的是当前价格。One of these rows contains the current price. 为了指示哪一行包含当前价格,该行的 IsCurrent 列的值设置为 1。To indicate which row contains the current price, the value of the IsCurrent column for that row is set to 1.

该数据库每周会生成一个 WeeklyChanges 表,其中包含该周的价格更改以及该周添加的新书。Every week, the database generates a WeeklyChanges table that contains price changes for the week and new books that were added during the week. 使用一个 MERGE 语句可以将 WeeklyChanges 表中的更改应用到 DimBook 表中。By using a single MERGE statement, you can apply the changes in the WeeklyChanges table to the DimBook table. MERGE 语句会为新添加的书插入新行,对于价格已更改的现有书的行,MERGE 语句会将这些行的 IsCurrent 列更新为 0。The MERGE statement inserts new rows for newly-added books, and updates the IsCurrent column to 0 for rows of existing books whose prices have changed. MERGE 语句还会为价格已更改的书插入新行,并会将这些新行的 IsCurrent 列的值设置为 1。The MERGE statement also inserts new rows for books whose prices have changed, and for these new rows, sets the value of the IsCurrent column to 1.

将具有新数据的表与旧表合并Merge a Table with New Data Against the Old Table

该数据库使用“开放式架构”(即,包含各属性的名称-值对的表)对对象的属性进行建模。The database models the properties of an object by using an "open schema," that is, a table contains name-value pairs for each property. “属性”表包含三列:EntityID、PropertyID 和 Value。The Properties table has three columns: EntityID, PropertyID, and Value. NewProperties 表是 Properties 表的更新版本,应与 Properties 表保持同步。A NewProperties table that is a newer version of the table has to be synchronized with the Properties table. 若要同步这两个表,可以使用一个 MERGE 语句执行以下操作:To synchronize these two tables, you can use a single MERGE statement to perform the following operations:

  • 从 Properties 表中删除 NewProperties 表中不存在的属性。Delete properties from the Properties table if they are absent from the NewProperties table.

  • 使用 NewProperties 表中找到的新值更新 Properties 表中的属性值。Update values for properties that are in the Properties table with new values found in the NewProperties table.

  • 插入在 NewProperties 表中存在但在 Properties 表中不存在的新属性。Insert new properties for properties that are in the NewProperties table but are not found in the Properties table.

此方法在类似复制方案的方案中非常有用,此方案的目标是使两台服务器上的两个表中的数据保持一致。This approach is useful in scenarios that resemble replication scenarios, where the objective is to keep data in two tables on two servers synchronized.

跟踪库存Track Inventory

Inventory 数据库具有一个 ProductsInventory 表,该表具有 ProductID 列和 StockOnHand 列。The Inventory database has a ProductsInventory table that has ProductID and StockOnHand columns. 具有 ProductID、CustomerID 和 Quantity 列的 Shipments 表跟踪对客户的产品发货情况。A Shipments table with ProductID, CustomerID, and Quantity columns tracks shipments of products to customers. ProductInventory 表应根据 Shipments 表中的信息每天更新。The ProductInventory table has to be updated daily based on information in the Shipments table. 根据发货情况,一个 MERGE 语句可以降低 ProductInventory 表中的库存。A single MERGE statement can reduce the inventory in the ProductInventory table based on the shipments made. 如果某产品的库存已降低为 0,该 MERGE 语句还可以从 ProductInventory 表中删除该产品行。If the inventory for a product has been reduced to 0, that MERGE statement can also delete that product row from the ProductInventory table.