我目前正在使用下面的合并代码将数据从源迁移到目标。我有一个新的需求,一旦在目标上执行了更新/插入,我就需要扩展下面的代码,以便从源中删除记录。是否可以使用merge(我在网上看到的所有示例都是在目标中执行del/insert/update,而不是在源中)
MERGE Target1 AS T
USING Source1 AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
THEN DELETE ;
发布于 2011-09-07 18:30:39
您可以使用output子句将修改/插入的行捕获到表变量,并在merge之后与delete语句一起使用。
DECLARE @T TABLE(EmployeeID INT);
MERGE Target1 AS T
USING Source1 AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
THEN DELETE
OUTPUT S.EmployeeID INTO @T;
DELETE Source1
WHERE EmployeeID in (SELECT EmployeeID
FROM @T);
发布于 2014-04-02 17:59:53
回答很好,但是您的代码将从目标表中删除行,这里有一个示例,您可以在不影响目标表的情况下从源目标中删除行:
if OBJECT_ID('audit.tmp1') IS NOT NULL
DROP TABLE audit.tmp1
select *
into audit.tmp1
from
(
select 1 id, 'aa' nom, convert(date,'2014-01-01') as dd UNION ALL
select 2 id, 'bb' nom, convert(date,'2013-07-12') as dd UNION ALL
select 3 id, 'cc' nom, convert(date,'2012-08-21') as dd UNION ALL
select 4 id, 'dd' nom, convert(date,'2011-11-15') as dd UNION ALL
select 5 id, 'ee' nom, convert(date,'2010-05-16') as dd ) T
if OBJECT_ID('audit.tmp2') IS NOT NULL
DROP TABLE audit.tmp2
select *
into audit.tmp2
from
(
select 1 id, 'aAa' nom, convert(date,'2014-01-14') as dd UNION ALL
select 2 id, 'bbB' nom, convert(date,'2013-06-13') as dd UNION ALL
select 4 id, 'dDD' nom, convert(date,'2012-11-05') as dd UNION ALL
select 6 id, 'FFf' nom, convert(date,'2014-01-12') as dd) T
SELECT * FROM audit.tmp1 order by 1
SELECT * FROM audit.tmp2 order by 1
DECLARE @T TABLE(ID INT);
MERGE audit.tmp2 WITH (HOLDLOCK) AS T
USING (SELECT * FROM audit.tmp1 WHERE nom <> 'dd') AS S
ON (T.id = S.id)
WHEN NOT MATCHED BY TARGET
THEN INSERT(id, nom, dd) VALUES(S.id, S.nom, S.dd)
WHEN MATCHED
THEN UPDATE SET T.nom = S.nom, T.dd = S.dd
WHEN NOT MATCHED BY SOURCE
THEN UPDATE SET T.id = T.id OUTPUT S.id INTO @T;
DELETE tmp1
FROM audit.tmp1
INNER JOIN
@T AS DEL
ON DEL.id = tmp1 .id
SELECT * FROM audit.tmp1 ORDER BY 1
SELECT * FROM audit.tmp2 ORDER BY 1
我希望这能对你有所帮助。
发布于 2019-06-16 12:26:26
在我们的示例中,我们希望使用MERGE将内部数据库与不同结构的外部源同步。自动级联设置不是一个选项,因为我们喜欢许多周期性的关系,而且,真的,我们不喜欢那种廉价的权力掌握在心怀不满的员工手中。在父行的子行消失之前,不能删除父行。
所有这一切都是通过使用表值参数的闪电般的快速合并来完成的。到目前为止,它们以极低的应用程序内存开销提供了最好的性能。
合并订单数据的分散建议...
CREATE PROCEDURE MyOrderMerge @SourceValues [MyOrderSqlUserType] READONLY
AS
BEGIN
DECLARE @LiveRows TABLE (MergeAction VARCHAR(20), OrderId INT);
DECLARE @DeleteCount INT;
SET @DeleteCount = 0;
MERGE INTO [Order] AS [target]
USING ( SELECT sv.OrderNumber,
c.CustomerId,
st.ShipTypeId
sv.OrderDate,
sv.IsPriority
FROM @SourceValues sv
JOIN [Customer] c ON sv.[CustomerName] = c.[CustomerName]
JOIN [ShipType] st ON ...
) AS [stream]
ON [stream].[OrderNumber] = [target].[SourceOrderNumber]
WHEN MATCHED THEN
UPDATE
...
WHEN NOT MATCHED BY TARGET THEN
INSERT
---
-- Keep a tally of all active source records
-- SQL Server's "INSERTED." prefix encompases both INSERTed and UPDATEd rows <insert very bad words here>
OUTPUT $action, INSERTED.[OrderId] INTO @LiveRows
; -- MERGE has ended
-- Delete child OrderItem rows before parent Order rows
DELETE FROM [OrderItem]
FROM [OrderItem] oi
-- Delete the Order Items that no longer exist at the source
LEFT JOIN @LiveRows lr ON oi.[OrderId] = lr.[OrderId]
WHERE lr.OrderId IS NULL
;
SET @DeleteCount = @DeleteCount + @@ROWCOUNT;
-- Delete parent Order rows that no longer have child Order Item rows
DELETE FROM [Order]
FROM [Order] o
-- Delete the Orders that no longer exist at the source
LEFT JOIN @LiveRows lr ON o.[OrderId] = lr.[OrderId]
WHERE lr.OrderId IS NULL
;
SET @DeleteCount = @DeleteCount + @@ROWCOUNT;
SELECT MergeAction, COUNT(*) AS ActionCount FROM @LiveRows GROUP BY MergeAction
UNION
SELECT 'DELETE' AS MergeAction, @DeleteCount AS ActionCount
;
END
一切都是在一个甜蜜的循环中完成的-dee-loop流式往返,并在关键索引上进行了高度优化。即使内部主键值在源中是未知的,合并操作也会使它们可用于删除操作。
Customer MERGE使用不同的@LiveRows表结构,从而使用不同的OUTPUT语句和不同的DELETE语句...
CREATE PROCEDURE MyCustomerMerge @SourceValues [MyCustomerSqlUserType] READONLY
AS
BEGIN
DECLARE @LiveRows TABLE (MergeAction VARCHAR(20), CustomerId INT);
DECLARE @DeleteCount INT;
SET @DeleteCount = 0;
MERGE INTO [Customer] AS [target]
...
OUTPUT $action, INSERTED.[CustomerId] INTO @LiveRows
; -- MERGE has ended
-- Delete child OrderItem rows before parent Order rows
DELETE FROM [OrderItem]
FROM [OrderItem] oi
JOIN [Order] o ON oi.[OrderId] = o.[OrderId]
-- Delete the Order Items that no longer exist at the source
LEFT JOIN @LiveRows lr ON o.[CustomerId] = lr.[CustomerId]
WHERE lr.CustomerId IS NULL
;
SET @DeleteCount = @DeleteCount + @@ROWCOUNT;
-- Delete child Order rows before parent Customer rows
DELETE FROM [Order]
FROM [Order] o
-- Delete the Orders that no longer exist at the source
LEFT JOIN @LiveRows lr ON o.[CustomerId] = lr.[CustomerId]
WHERE lr.CustomerId IS NULL
;
SET @DeleteCount = @DeleteCount + @@ROWCOUNT;
-- Delete parent Customer rows that no longer have child Order or grandchild Order Item rows
DELETE FROM [Customer]
FROM [Customer] c
-- Delete the Customers that no longer exist at the source
LEFT JOIN @LiveRows lr ON c.[CustomerId] = lr.[CustomerId]
WHERE lr.CustomerId IS NULL
;
SET @DeleteCount = @DeleteCount + @@ROWCOUNT;
SELECT MergeAction, COUNT(*) AS ActionCount FROM @LiveRows GROUP BY MergeAction
UNION
SELECT 'DELETE' AS MergeAction, @DeleteCount AS ActionCount
;
END
设置和维护有点痛苦--但所获得的效率是值得的。
https://stackoverflow.com/questions/7331725
复制相似问题