葡京网投哪个正规 > 新葡亰-编程 > 判定条件,SQLServer之merge函数用法

原标题:判定条件,SQLServer之merge函数用法

浏览次数:123 时间:2020-03-25

核心提示:就像标题呈现的一样,SQL Server 2008中的MERGE语句能做很多事情

 

 

其中最后语句分号不可以省略,且源表既可以是一个表也可以是一个子查询语句

语句;

  • The MERGE SQL statement requires a semicolon (;) as a statement terminator. Otherwise Error 10713 is raised when a MERGE statement is executed without the statement terminator.
  • When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.
  • At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However a variable cannot be updated more than once in the same MATCHED clause.
  • Of course it’s obvious, but just to mention, the person executing the MERGE statement should have SELECT Permission on the SOURCE Table and INSERT, UPDATE and DELETE Permission on the TARGET Table.
  • MERGE SQL statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
  • MERGE SQL statement takes same kind of locks minus one Intent Shared (IS) Lock that was due to the select statement in the ‘IF EXISTS’ as we did in previous version of SQL Server.
  • For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify.
  • 在 Merge Matched 操作中,只能允许执行 UPDATE 或者 DELETE 语句。
  • 在 Merge Not Matched 操作中,只允许执行 INSERT 语句。
  • 一个 Merge 语句中出现的 Matched 操作,只能出现一次 UPDATE 或者 DELETE 语句,否则就会出现下面的错误 - An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.
  • Merge 语句最后必须包含分号,以 ; 结束。

WHEN NOT MATCHED BY TARGET
表示目标表不匹配,BY TARGET是默认的,所以上面我们直接使用WHEN NOT MATCHED THEN
WHEN NOT MATCHED BY SOURCE
表示源表不匹配,即目标表中存在,源表中不存在的情况。

WHEN NOT MATCHED BY SOURCE

 

id          val

 

MERGE ProductNew AS d USING Product AS s ON s.ProductID = d.ProductId WHEN NOT MATCHED THEN INSERT( ProductID,ProductName,Price) VALUES(s.ProductID,s.ProductName,s.Price);

根据在另一个表中找到的差异在一个表中插入、更新或删除行,可以对两个表进行同步。

CREATE TABLE test_from (id INT, val VARCHAR(20));
-- 目标表
CREATE TABLE test_to (id INT, val VARCHAR(20));
-- 插入源表
INSERT INTO test_from VALUES (1, 'A');
INSERT INTO test_from VALUES (2, 'B');
-- 合并 源表到目标表

merge icr_codemap_bak as a
using icr_codemap as b
on a.COLNAME = b.COLNAME and a.ctcode = b.ctcode
when matched and b.pbcode <> a.pbcode
then update set a.pbcode = b.pbcode
when not matched
then insert values(b.colname,b.ctcode,b.pbcode,b.note)
;
可以比对字段不一致进行更新
这个是MSDN的网址

USING 源表

 

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

在 Merge Matched 操作中,只能允许执行 UPDATE 或者 DELETE 语句。
在 Merge Not Matched 操作中,只允许执行 INSERT 语句。
一个 Merge 语句中出现的 Matched 操作,只能出现一次 UPDATE 或者 DELETE 语句,否则就会出现下面的错误 - An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.
Merge 语句最后必须包含分号,以 ; 结束。

MERGE ProductNew AS d USING Product AS s ON s.ProductID = d.ProductId WHEN NOT MATCHED BY TARGET THEN INSERT( ProductID,ProductName,Price) VALUES(s.ProductID,s.ProductName,s.Price) WHEN NOT MATCHED BY SOURCE THEN DELETE WHEN MATCHED THEN UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price;

EXECUTE Production.usp_UpdateInventory '20030501'
B. 借助派生的源表,使用 MERGE 对目标表执行 UPDATE 和 INSERT 操作
下面的示例使用 MERGE 以更新或插入行的方式来修改 SalesReason 表。当源表中的 NewName 值与目标表 (SalesReason) 的 Name 列中的值匹配时,就会更新此目标表中的 ReasonType 列。当 NewName 的值不匹配时,就会将源行插入到目标表中。此源表是一个派生表,它使用 Transact-SQL 行构造函数功能指定源表的多个行。有关在派生表中使用行构造函数的详细信息,请参阅 FROM (Transact-SQL)。

          1 A

主要用法:
merge无法多次更新同一行,也无法更新和删除同一行
当源表和目标表不匹配时:
葡京正网网投 ,若数据是源表有目标表没有,则进行插入操作
若数据是源表没有而目标表有,则进行更新或者删除数据操作
当源表和目标表匹配时:
进行更新操作或者删除操作

--目标表 CREATE TABLE ProductNew ( ProductID varchar(7) NOT NULL PRIMARY KEY, ProductName varchar(100) NOT NULL, Price decimal(13,2) DEFAULT 0 );

A. 使用 MERGE 在单个语句中对表执行 UPDATE 和 DELETE 操作
下面的示例使用 MERGE 根据 SalesOrderDetail 表中已处理的订单,每天更新 AdventureWorks 示例数据库中的 ProductInventory 表。通过减去每天对 SalesOrderDetail 表中的每种产品所下的订单数,更新 ProductInventory 表的 Quantity 列。如果某种产品的订单数导致该产品的库存量下降到 0 或更少,则会从 ProductInventory 表中删除该产品对应的行。


葡京网投哪个正规 ,when matched 这个子句可以有两个,当有两个时,第一个子句必须是when matched and condition且两个matched子句只会执行一个,且两个子句必须
是一个update和一个delete操作
when not matched by source和上面类似

回到我们的示例,显然Product与ProductNew表的MERGE匹配条件为主键ProductID字段,初始情况下,ProductNew表为空,此时肯定执行的是WHEN NOT MATCHED THEN后的语句,我们先只考虑源表递增的情况,MERGE语句如下:

 Notes

WHEN NOTMATCHED BY SOURCE - 如果匹配不成功 (SOURCE 中没有这一条记录但是 TARGET 表有,说明 SOURCE 表可能把这条数据删除了,所以 TARGET 也应该删除)

MERGE 目标表
USING 源表
ON 匹配条件
WHEN MATCHED THEN
语句
WHEN NOT MATCHED THEN
语句;

WHEN NOT MATCHED THEN

 
USE AdventureWorks;
GO
MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
       AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
 UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN
 INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)
OUTPUT $action, inserted.*, deleted.*;
C. 将 MERGE 语句的执行结果插入到另一个表中
下例捕获从 MERGE 语句的 OUTPUT 子句返回的数据,并将该数据插入另一个表。MERGE 语句根据在 SalesOrderDetail 表中处理的订单,更新 ProductInventory 表的 Quantity 列。本示例捕获已更新的行,并将这些行插入用于跟踪库存变化的另一个表中。

-- 再次检查 目标表数据.

运行后2行受影响,我们已经将Product表的数据同步到了ProductNew表。

 
USE AdventureWorks;
GO
CREATE TABLE Production.UpdatedInventory
    (ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,
     CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));
GO
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM
(    MERGE Production.ProductInventory AS pi
     USING (SELECT ProductID, SUM(OrderQty)
            FROM Sales.SalesOrderDetail AS sod
            JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate BETWEEN '20030701' AND '20030731'
            GROUP BY ProductID) AS src (ProductID, OrderQty)
     ON pi.ProductID = src.ProductID
    WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
 AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
GO

DELETE FROM test_from WHERE id = 2;

--源表 CREATE TABLE Product ( ProductID varchar(7) NOT NULL PRIMARY KEY, ProductName varchar(100) NOT NULL, Price decimal(13,2) DEFAULT 0 );

 
USE AdventureWorks;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
    @OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
    JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    AND soh.OrderDate = @OrderDate
    GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
    THEN DELETE
WHEN MATCHED
    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
                    target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
    Deleted.Quantity, Deleted.ModifiedDate;
GO

 

格外强调一点,MERGE语句最后的分号是不能省略的!

WHEN MATCHED THEN UPDATE SET test_to.val = test_from.val   -- 匹配的时候,更新

以下SQL创建示例表:

SELECT * FROM test_to;

INSERT INTO Product Values (4100037,优盘,50), (4100038,鼠标,30);

 

表示目标表不匹配,BY TARGET是默认的,所以上面我们直接使用WHEN NOT MATCHED THEN

-- 第一次检查 目标表数据.

就像标题呈现的一样,SQL Server 2008中的MERGE语句能做很多事情,它的功能是根据源表对目标表执行插入、更新或删除操作。最典型的应用就是进行两个表的同步。

id          val

表示源表不匹配,即目标表中存在,源表中不存在的情况。

WHEN NOTMATCHED BY TARGET - 如果匹配不成功 (TARGET 中没有这一条记录但是 SOURCE 表有,说明 SOURCE 表多了新数据因此应该插入到 TARGET 表中)

语句

--源表

MERGE ProductNew AS d USING Product AS s ON s.ProductID = d.ProductId WHEN NOT MATCHED THEN INSERT( ProductID,ProductName,Price) VALUES(s.ProductID,s.ProductName,s.Price) WHEN MATCHED THEN UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price;

-- 删除源表

上面已经使用到MERGE语句中的INSERT、UPDATE、DELETE语句,这足够完成大多数的同步功能了。当然,MERGE语句还有很多的选项,在此不做详述,请参考MSDN.

MERGE test_to USING test_from

ON 匹配条件

          3 C

现在做个破坏,我们将410037产品删除掉:

MERGE test_to 

下面再来关注MERGE语句的基本语法:

          2 B

现在,我们更新Product表4100037产品的价格,将其修改为55:

SELECT * FROM test_to;

WHEN MATCHED THEN

INSERT INTO test_from VALUES (3, 'C');

WHEN NOT MATCHED BY TARGET

还要注意的是有一些限制条件:

UPDATE Product SET Price=55 WHERE ProductID=4100037;

WHEN MATCHED - 如果匹配成功,即关联条件成功 (这时就应该将 SOURCE 中其它的所有字段值更新到 TARGET 表中)

以上是MERGE的最最基本的语法,语句执行时根据匹配条件的结果,如果在目标表中找到匹配记录则执行WHEN MATCHED THEN后面的语句,如果没有找到匹配记录则执行WHEN NOT MATCHED THEN后面的语句。注意源表可以是表,也可以是一个子查询语句。

UPDATE test_from SET val = 'A2' WHERE id = 1;

明显,上面给出的MERGE语句无法同步这种情况,再次回到MERGE语句的定义,对MERGE的WHEN NOT MATCHED THEN语句稍作扩展:

ON ( test_to.id = test_from.id )    -- 条件是 id 相同

MERGE 目标表

同步操作

执行后2行受影响,为什么是两行呢?因为我们的匹配条件只是按ProductID来关联的,这样匹配出来的记录为2行。另外,我们的UPDATE语句里面没有更新ProductID字段,因为这是完全没必要的。

WHEN NOT MATCHED BY SOURCE THEN DELETE; -- 目标表有,源表没有,目标表该数据删除.

现在我们要完成源表DELETE后,目标表的同步动作,MERGE语句如下:

ON ( test_to.id = test_from.id )    -- 条件是 id 相同
WHEN MATCHED THEN UPDATE SET test_to.val = test_from.val   -- 匹配的时候,更新
WHEN NOT MATCHED THEN INSERT VALUES(test_from.id, test_from.val) -- 源表有,目标表没有,插入
WHEN NOT MATCHED BY SOURCE THEN DELETE; -- 目标表有,源表没有,目标表该数据删除.

我们也希望每天同步的时候应该将更新后的价格同步到ProductNew表,显然此时在MERGE语句中应该添加WHEN MATCHED THEN 语句,该语句来更新ProductNew表的价格,添加匹配更新后的MERGE语句:

例子:

DELETE Product WHERE ProductID=4100037;

-- 合并 源表到目标表

下面通过一个简单示例来演示MERGE语句的使用方法,假设数据库中有两个表Product及ProductNew,我们的任务是将Product的数据同步到ProductNew。

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

 

-- 更新源表

MERGE INTO @TargetTable AS T           
USING @SourceTable AS S ON T.ID = S.ID WHEN MATCHED THEN UPDATE SET T.DSPT = S.DSPT WHEN NOT MATCHED BY TARGET THEN INSERT VALUES(S.ID,S.DSPT) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $ACTION AS [ACTION],
   Deleted.ID AS 'Deleted ID',
   Deleted.DSPT AS 'Deleted Description',
   Inserted.ID AS 'Inserted ID',
   Inserted.DSPT AS 'Inserted Description' INTO @Log;

          1 A2

USING 与源表连接 ON 关联的条件

 

-- 插入源表

WHEN NOT MATCHED THEN INSERT VALUES(test_from.id, test_from.val) -- 源表有,目标表没有,插入

USING test_from

MERGE INTO - 数据的目的地,将数据最终 MERGE 到的表对象

本文由葡京网投哪个正规发布于新葡亰-编程,转载请注明出处:判定条件,SQLServer之merge函数用法

关键词:

上一篇:excel的数据转换_数据库技巧,将Excel中的数据导入到MSSQLServer2000

下一篇:基本语法,实用工具葡京网投哪个正规