MSSQL 读书笔记之MERGE 语句使用
DataBase
2015-07-16
387
0
常用语法
MERGE INTO <target table> AS TGT USING <SOURCE TABLE> AS SRC ON <merge predicate> WHEN MATCHED [AND <predicate>] -- 允许两个子句: THEN <action> -- UPDATE 和 DELETE WHEN NOT MATCHED [BY TARGET] [AND <predicate>] -- 允许一个子句: THEN INSERT... –- INSERT WHEN NOT MATCHED BY SOURCE [AND <predicate>] -- 允许两个子句: THEN <action>; -- UPDATE 和 DELETE
-- clear table and reset sequence if the already exist --TRUNCATE TABLE Sales.MyOrders; --ALTER SEQUENCE Sales.SeqOrderIDs RESTART WITH 1; -- create table and sequence if they don't already exist IF OBJECT_ID(N'Sales.MyOrders', N'U') IS NOT NULL DROP TABLE Sales.MyOrders; IF OBJECT_ID(N'Sales.SeqOrderIDs', N'SO') IS NOT NULL DROP SEQUENCE Sales.SeqOrderIDs; CREATE SEQUENCE Sales.SeqOrderIDs AS INT MINVALUE 1 CYCLE; CREATE TABLE Sales.MyOrders ( orderid INT NOT NULL CONSTRAINT PK_MyOrders_orderid PRIMARY KEY CONSTRAINT DFT_MyOrders_orderid DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs), custid INT NOT NULL CONSTRAINT CHK_MyOrders_custid CHECK(custid > 0), empid INT NOT NULL CONSTRAINT CHK_MyOrders_empid CHECK(empid > 0), orderdate DATE NOT NULL );
-- SELECT without FROM DECLARE @orderid AS INT = 1, @custid AS INT = 1, @empid AS INT = 2, @orderdate AS DATE = '20120620'; SELECT * FROM (SELECT @orderid, @custid, @empid, @orderdate ) AS SRC( orderid, custid, empid, orderdate ); GO -- table value constructor DECLARE @orderid AS INT = 1, @custid AS INT = 1, @empid AS INT = 2, @orderdate AS DATE = '20120620'; SELECT * FROM (VALUES(@orderid, @custid, @empid, @orderdate)) AS SRC( orderid, custid, empid, orderdate);
-- update where exists (only if different), insert where not exists, -- delete when exists in target but not in source DECLARE @orderid AS INT = 1, @custid AS INT = 1, @empid AS INT = 2, @orderdate AS DATE = '20120620'; MERGE INTO Sales.MyOrders WITH (HOLDLOCK) AS TGT --这里 HOLDLOCK或者SERIALIZABLE作用都是一样的,防止MERGE冲突 USING (VALUES(@orderid, @custid, @empid, @orderdate)) --比如某个主键ID不存在于目标表。有两个进程P1和P2使用MERGE同时处理这个ID AS SRC( orderid, custid, empid, orderdate) --P1插入了这个ID的同时,P2也插入该ID,此时P2就会因违背主键约束而失败。 ON SRC.orderid = TGT.orderid --WHEN MATCHED THEN UPDATE --这里有一个性能问题,如果第二次执行该语句,碰到ID一模一样数据,会再一次更新。 WHEN MATCHED AND ( TGT.custid <> SRC.custid --这样会损耗性能,可以额外加判断来减少性能损失。 OR TGT.empid <> SRC.empid OR TGT.orderdate <> SRC.orderdate) THEN UPDATE SET TGT.custid = SRC.custid, TGT.empid = SRC.empid, TGT.orderdate = SRC.orderdate WHEN NOT MATCHED THEN INSERT --如果目标表不存在则插入 VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate); WHEN NOT MATCHED BY SOURCE THEN --如果目标表存在但源表不存在则删除 DELETE; OUTPUT $action AS the_action, COALESCE(inserted.orderid, deleted.orderid) AS orderid
TGT.custid = SRC.custid OR (TGT.custid IS NULL AND SRC.custid IS NOT NULL) OR (TGT.custid IS NOT NULL AND SRC.custid IS NULL).
MERGE INTO Sales.MyOrders AS TGT USING Sales.Orders AS SRC ON SRC.orderid = TGT.orderid AND shipcountry = N'Norway' WHEN MATCHED AND ( TGT.custid <> SRC.custid OR TGT.empid <> SRC.empid OR TGT.orderdate <> SRC.orderdate) THEN UPDATE SET TGT.custid = SRC.custid, TGT.empid = SRC.empid, TGT.orderdate = SRC.orderdate WHEN NOT MATCHED THEN INSERT VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);
之所以报错,是因为ON字句并不会过滤数据,如果shipcountry 不是Norway,则直接执行 NOT MATCHED ,此时因为目标表里面已经有了数据,导致了主键约束错误
解决方法只能事先过滤,然后再执行MERGE
-- 使用CTE WITH SRC AS ( SELECT * FROM Sales.Orders WHERE shipcountry = N'Norway' ) MERGE INTO Sales.MyOrders AS TGT USING SRC ON SRC.orderid = TGT.orderid WHEN MATCHED AND ( TGT.custid <> SRC.custid OR TGT.empid <> SRC.empid OR TGT.orderdate <> SRC.orderdate) THEN UPDATE SET TGT.custid = SRC.custid, TGT.empid = SRC.empid, TGT.orderdate = SRC.orderdate WHEN NOT MATCHED THEN INSERT VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);-- 使用派生表 MERGE INTO Sales.MyOrders AS TGT USING ( SELECT * FROM Sales.Orders WHERE shipcountry = N'Norway' ) AS SRC ON SRC.orderid = TGT.orderid WHEN MATCHED AND ( TGT.custid <> SRC.custid OR TGT.empid <> SRC.empid OR TGT.orderdate <> SRC.orderdate) THEN UPDATE SET TGT.custid = SRC.custid, TGT.empid = SRC.empid, TGT.orderdate = SRC.orderdate WHEN NOT MATCHED THEN INSERT VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);
SELECT ProductID , Name INTO Products FROM Production.Product SELECT ProductID AS "@id" , Name AS "@name" FROM Products WHERE Name LIKE '_A%' FOR XML PATH('product') , ROOT('products'); DECLARE @Xml XML = N' <products> <product id="843" name="Cable Lock" /> <product id="873" name="Patch Kit/8 P11atches" /> <product id="875" delete="true" name="Racing Socks, L" /> <product id="874" name="Racing Socks, M" /> <product id="846" name="Taillights - Battery-Pow1ered" /> <product name="Wdsfe - 30 oz." /> </products>'; WITH src AS ( SELECT xt.xc.value('@id', 'INT') AS ProductID , xt.xc.value('@name', 'NVARCHAR(1000)') AS Name , ISNULL(xt.xc.value('@delete', 'BIT'), 0) AS DoDelete FROM @Xml.nodes('/products/product') AS xt ( xc ) ) MERGE INTO Products AS dest USING src ON src.ProductID = dest.ProductID WHEN NOT MATCHED THEN INSERT ( Name ) VALUES ( src.Name ) WHEN MATCHED AND src.DoDelete = 0 THEN UPDATE SET Name = src.Name WHEN MATCHED AND src.DoDelete = 1 THEN DELETE ;
0条评论