巧用MERGE同时插入、更新和删除

原创|其它|编辑:郝浩|2009-04-30 13:28:29.000|阅读 1467 次

概述:在一个典型的数据仓库应用程序中,你往往需要在ETL循环中通过匹配SOURCE表的记录执行TARGET表上的INSERT,UPDATE和DELETE操作。例如,一张产品维度表包含产品的信息;你需要在这张表中同步源表的最新信息。

# 界面/图表报表/文档/IDE等千款热门软控件火热销售中 >>

  随着SQL Server 2008的出现,现在你可以使用MERGE SQL命令在一个语句中执行这些操作。这个新的命令类似于Oracle的UPSERT命令(融合了单词UPDAT和INSERT);它插入原先不存在的行并更新已经存在的行。随着MERGE SQL命令的引进,开发人员可以更加有效地处理数据仓库情况,例如检查某一行是否存在,然后执行一个插入或者更新或者删除。

  基本上,MERGE语句根据你指定的情况和来自源表的数据是否已经在目标表中存在把来自一个元结果集的数据合并到一个目标表中。新的SQL命令结合单个元语句中有条件的INSERT,UPDATE和DELETE命令的次序,这取决于一条记录是否存在。这个新的MERGE SQL命令类似于下面:

  MERGE [AS TARGET]

  USING [AS SOURCE]

  ON

  [WHEN MATCHED

  THEN ]

  [WHEN NOT MATCHED [BY TARGET]

  THEN ]

  [WHEN NOT MATCHED BY SOURCE

  THEN ];
 
  MERGE语句基本上作为相同语句内部的插入、更新和删除语句来执行,你指定个“源”记录集和一个“目标”表,然后连接两张表,接着,你指定当两个数据之间的记录是否记录时发生的数据修改类型。MERGE很有用,尤其是载入数据仓库表时,因为这些表可能非常大,并且在行目前是否存在时要求采取具体的行动。

  把它放在一起

  在这个例子中,我将把一个产品表作为一个目标表,把UpdatedProducts表作为一个包含产品更新列表的源表。接着,我将使用MERGE SQL命令让目标表同步于源表。

  首先,让我们创建一个目标表和一个源表,并把数据配置到这些表中。

  MERGE SQL statement - Part 1

  --Create a target table

  CREATE TABLE Products

  (

  ProductID INT PRIMARY KEY,

  ProductName VARCHAR(100),

  Rate MONEY

  )

  GO

  --Insert records into target table

  INSERT INTO Products

  VALUES

  (1, 'Tea', 10.00),

  (2, 'Coffee', 20.00),

  (3, 'Muffin', 30.00),

  (4, 'Biscuit', 40.00)

  GO

  --Create source table

  CREATE TABLE UpdatedProducts

  (

  ProductID INT PRIMARY KEY,

  ProductName VARCHAR(100),

  Rate MONEY

  )

  GO

  --Insert records into source table

  INSERT INTO UpdatedProducts

  VALUES

  (1, 'Tea', 10.00),

  (2, 'Coffee', 25.00),

  (3, 'Muffin', 35.00),

  (5, 'Pizza', 60.00)

  GO

  SELECT * FROM Products

  SELECT * FROM UpdatedProducts

  GO

  接着,我将使用MERGE SQL命令来使目标表同步于来自源表的刷新数据。

    MERGE SQL statement - Part 2

  --Synchronize the target table with

  --refreshed data from source table

  MERGE Products AS TARGET

  USING UpdatedProducts AS SOURCE

  ON (TARGET.ProductID = SOURCE.ProductID)

  --When records are matched, update

  --the records if there is any change

  WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName

  OR TARGET.Rate <> SOURCE.Rate THEN

  UPDATE SET TARGET.ProductName = SOURCE.ProductName,

  TARGET.Rate = SOURCE.Rate

  --When no records are matched, insert

  --the incoming records from source

  --table to target table

  WHEN NOT MATCHED BY TARGET THEN

  INSERT (ProductID, ProductName, Rate)

  VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)

  --When there is a row that exists in target table and

  --same record does not exist in source table

  --then delete this record from target table

  WHEN NOT MATCHED BY SOURCE THEN

  DELETE

  --$action specifies a column of type nvarchar(10)

  --in the OUTPUT clause that returns one of three

  --values for each row: 'INSERT', 'UPDATE', or 'DELETE',

  --according to the action that was performed on that row

  OUTPUT $action,

  DELETED.ProductID AS TargetProductID,

  DELETED.ProductName AS TargetProductName,

  DELETED.Rate AS TargetRate,

  INSERTED.ProductID AS SourceProductID,

  INSERTED.ProductName AS SourceProductName,

  INSERTED.Rate AS SourceRate;

  SELECT @@ROWCOUNT;

  GO

  当完成上面语句的运行,输出结果将如下。这里有两个更新,一个删除和一个插入。

图一

  如果我们查询来自产品表的所有记录,我们将看到最后的结果。我们可以看到Coffee比例从20.00 更新到25.00,Muffin比例从30.00更新道 35.00,Biscuit被删除,Pizza被插入。

图二

  注意

  MERGE SQL需要一个分号(;)作为一个语句终止的符号,否则当执行一个没有语句终止符号的MERGE语句时将出现Error 10713。

  当使用MERGE之后,@@ROWCOUNT把插入、更新、删除的行总数返回给用户。

  当使用MERGE语句时,至少需要指定其中一个MATCHE从句;MATCHED从句可以按照任何顺序指定。尽管如此,一个变量在相同的MATCHED从句中不能更新多于一次。

  当然下面这一点是显而易见的,但是也需要提一提,执行MERGE语句的人员应该具有源表上的SELECT权限和目标表上的插入、更新和删除操作。

  MERGE SQL语句改进了性能,因为所有的数据只读和处理一次,然而在前面的版本中,需要写三个不同的语句来处理三种不同的活动(插入、更新或者删除),在这些案例中,源和目标表中的数据都需要进行多次评估和处理;每个语句至少一次。

  MERGE SQL语句采取相同类型的锁,除了意向共享锁,这是因为“如果存在”中的查询语句,正如我们在SQL Server前面版本中所做的。

  对于MERGE语句中的每一个插入、更新或者删除操作,SQL Server激活任何在目标表中的相应AFTER触发器,但是不能保证激活触发器的哪个行为先进性或者后进行。为相同行为而定义的触发器履行你指定的顺序。


标签:

本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处、不得修改原文相关链接,如果存在内容上的异议请邮件反馈至chenjj@evget.com

文章转载自:IT专家网

为你推荐

  • 推荐视频
  • 推荐活动
  • 推荐产品
  • 推荐文章
  • 慧都慧问
扫码咨询


添加微信 立即咨询

电话咨询

客服热线
023-68661681

TOP