I have a table (DimBCAccountSchema) in SQL SSMS with the following structure:
| MasterTreeKey | ParentKey | Name | LineNo | Indentation | CreateDate | UpdateDate | AuditID |
|---|---|---|---|---|---|---|---|
| 22400 | NULL | Other Income | 530000 | 0 | 2023-05-22 09:40:53.7173015 | NULL | 0 |
| 22403 | 22400 | Compensation for damages | 2080000 | 1 | 2023-05-22 09:40:53.7173015 | NULL | 0 |
| 22404 | 22403 | Domestic | 1920000 | 2 | 2023-05-22 09:40:53.7173015 | NULL | 0 |
| 22405 | 22403 | Compensation for damages (PowerApp) | 1060000 | 2 | 2023-05-22 09:40:53.7173015 | NULL | 0 |
This table represents a tree of categories and subcategories, where some of them contain the others. In this example the category named 'Other Income' contains the category 'Compensation for damages', which at the same time contains both 'Domestic' and 'Compensation for damages(PowerApp)'. The "contained" relationship is established as follows: the ParentKey in each row coincides with the MasterKey of its parent row. At the same time, the column indentation represents the "depth" within the tree.
I want to use three different Merge StoredProcedures (one for each possible indentation level) to Delete-Insert-Update this table. In the first merge (uspLoadDboDimBCAccSchedule_0) rows with indentation 0 are loaded, and so for the rest. This is how the merge for indentation 0 looks like:
/****** Object: StoredProcedure [dbo].[uspLoadDboDimBCAccSchedule_0_copy] Script Date: 22.05.2023 10:38:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspLoadDboDimBCAccSchedule_0_copy]( @AuditID BIGINT
)
AS
SET NOCOUNT ON
DECLARE @MergeOutput TABLE ([DMLAction] VARCHAR(6));
DECLARE @vUnknownValue AS NVARCHAR(10) = 'N/A';
MERGE INTO dbo.DimBCAccountSchema AS TARGET USING(
SELECT DISTINCT
TD.[Description] AS [Name]
,TD.[LineNo] AS [LineNo]
,TD.Indentation AS [Indentation]
,ACS.[MasterTreeKey] AS [ParentKey]
FROM etl.RowNoTreeHelper_copy TD
LEFT JOIN DimBCAccountSchema ACS ON ACS.[LineNo] = TD.[Parent_LineNo]
WHERE TD.Indentation = 0
)
AS SOURCE
ON
SOURCE.[ParentKey] = TARGET.[ParentKey]
AND SOURCE.[LineNo] = TARGET.[LineNo]
AND SOURCE.[Indentation] = TARGET.[Indentation]
WHEN MATCHED AND (
TARGET.[Name] <> SOURCE.[Name]
)
THEN UPDATE SET
[ParentKey] = SOURCE.[ParentKey]
,[Name] = SOURCE.[Name]
,[LineNo] = SOURCE.[LineNo]
,[Indentation] = SOURCE.[Indentation]
,[UpdateDate] = SYSDATETIME()
,[AuditID] = @AuditID
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[ParentKey]
,[Name]
,[LineNo]
,[Indentation]
,[CreateDate]
,[AuditID]
)
VALUES(
[ParentKey]
,[Name]
,[LineNo]
,[Indentation]
,SYSDATETIME()
,@AuditID
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
My problem comes in the DELETE part of the MERGE. The rest works fine. When I run the previous code, the whole TARGET table gets deleted in the first place to get reinserted afterwards - with so many Inserts as rows there are in the SOURCE table - with the new data from the SOURCE table. The final TARGET rows and columns look fine, but I only want to remove rows which don't belong anymore, as well as update those which have to instead of deleting and reinserting them.
How can I change the DELETE part to satisfy my conditions?
P.S: The problem arises because I need some more conditions in the DELETE statement, but I'm not sure how to change it. If I understand it correctly, my MERGE runs through each row in the SOURCE table and compares it with each row in the TARGET table. If no match in the TARGET table is found for the SOURCE table row in question (which happens for every row in the TARGET table except for its copy in the TARGET table, in case it's already given), the TARGET table row gets deleted.
This means that after comparing the first row of SOURCE, all rows in TARGET except one get deleted. As the MERGE runs through the remaining rows in SOURCE, the same amount of rows gets added in TARGET because the corresponding rows got deleted during the first run.