August 1, 2019 at 5:55 pm
I'm familiar with CTE's which I have used a lot and I am somewhat familiar with the MERGE statement but I've never worked with these 2 together and now that we have recently (finally) upgraded to more recent builds of SQL Server I'm looking at something I need some help understanding.
With the understanding that #TRANSDETAIL_TEMP is a temp table created and populated with data does the below statement result in the table TRANSDETAIL being updated/inserted/deleted via the common table expression named TRANSDETAIL_CTE? Thats what it looks like to me but I've never seen a MERGE statement use a CTE as an alias (that may not be the correct term) for the table that you want to be the TARGET of the update/merge/delete.
WITH TRANSDETAIL_CTE ( Property , Account , Book , FinMonth , TranDate , TranType , TranID , TranDetailID , TranPerson , TranAmount , TranNumId , TranReference , TranNotes )
AS ( SELECT Property , Account , Book , FinMonth , TranDate , TranType , TranID , TranDetailID , TranPerson , TranAmount , TranNumId , TranReference , TranNotes
FROM TRANSDETAIL
WHERE 1 = 1
AND Property = 378
and FinMonth >= convert(datetime, '06/01/2019', 101)
)
MERGE TRANSDETAIL_CTE AS TARGET USING
( SELECT Property , Account , Book , FinMonth , TranDate , TranType , TranID , TranDetailID , TranPerson , TranAmount , TranNumId , TranReference , TranNotes
FROM #TRANSDETAIL_TEMP
WHERE Property = 378
AND FinMonth >= convert(datetime, '06/01/2019', 101)
) AS SOURCE
ON ( TARGET.Property = SOURCE.Property
AND TARGET.Account = SOURCE.Account
AND TARGET.Book = SOURCE.Book
AND TARGET.FinMonth = SOURCE.FinMonth
AND TARGET.TranID = SOURCE.TranID
AND IsNull(target.TranDetailID, 0) = IsNull(SOURCE.TranDetailID , 0)
AND TARGET.TranAmount = SOURCE.TranAmount
AND IsNull(target.TranNotes,'') = IsNull(SOURCE.TranNotes,'')
)
WHEN MATCHED THEN UPDATE SET TARGET.TranDate = SOURCE.TranDate,
TARGET.TranType = SOURCE.TranType,
TARGET.TranPerson = SOURCE.TranPerson,
TARGET.TranNumId = SOURCE.TranNumId,
TARGET.TranReference = SOURCE.TranReference,
TARGET.TranNotes = SOURCE.TranNotes
WHEN NOT MATCHED THEN INSERT ( Property , Account , Book , FinMonth , TranDate , TranType , TranID , TranDetailID , TranPerson , TranAmount , TranNumId , TranReference , TranNotes )
VALUES ( SOURCE.Property, SOURCE.Account, SOURCE.Book, SOURCE.FinMonth, SOURCE.TranDate, SOURCE.TranType, SOURCE.TranID, SOURCE.TranDetailID, SOURCE.TranPerson, SOURCE.TranAmount, SOURCE.TranNumId , SOURCE.TranReference, SOURCE.TranNotes )
WHEN NOT MATCHED BY SOURCE THEN DELETE;
go
Kindest Regards,
Just say No to Facebook!August 2, 2019 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 2, 2019 at 6:39 pm
Your code will be inserting, updating and deleting the results from the CTE, but it then doesn't do anything with the results of the CTE, not even selecting from it. Not sure what the purpose of this SQL is.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 2, 2019 at 6:54 pm
I'm just as puzzled by this as well but the only code I did not include was the creation of the #TRANSDETAIL_TEMP temp table and the 100 plus inserts into it as I felt those weren't relevant to the question about the merge with a CTE. So based on the code here which I captured using Profiler I thought that maybe the CTE was an alias for the table TRANSDETAIL and so any changes made to the CTE would effectively be made to the TRANSDETAIL table but it sounds like your saying that is not whats happening. Could it be that there may be more code after what I've listed here that just sin;t captured by PROFILERS SQL:STMNT and SQL:BATCH events?
What I do know is that by the end of the process I was tracing the end should be that some rows of data in the TRANSDETAIL are changed either via update or replacement (DELETE followed by INSERT) . In past iterations of the software (what I'm tracing) the TRANSDETAIL table would have a DELETE FROM based on some condition followed by INSERT FROM #TRANSDETAIL_TEMP. This recent update is the first time I've seen the use of MERGE. Knowing that and what I have so far can you see one or more addiotnal commands (I didn't get in Profiler) that would result in the same changes being pushed to the TRANSDETAIL table that were made to the CTE?
Thanks
Kindest Regards,
Just say No to Facebook!August 2, 2019 at 7:17 pm
Don't really need to know how or what built the #TRANSDETAIL_TEMP table.
The CTE of TRANSDETAIL_CTE is some what like a temp table of the results from that SQL.
Then you are doing a merge essentially on a temp table. It will not affect the TRANSDETAIL table. And what is in the TRANSDETAIL_CTE will be lost after the ';'.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 2, 2019 at 7:31 pm
To me this is what the code is doing, modified just to make it easier to understand:
/*WITH TRANSDETAIL_CTE (Property , Account , Book , FinMonth , TranDate , TranType , TranID , TranDetailID , TranPerson , TranAmount , TranNumId , TranReference , TranNotes )
AS (*/
SELECT Property , Account , Book , FinMonth , TranDate , TranType , TranID , TranDetailID , TranPerson , TranAmount , TranNumId , TranReference , TranNotes
INTO #TRANSDETAIL_CTE
FROM TRANSDETAIL
WHERE 1 = 1
AND Property = 378
and FinMonth >= convert(datetime, '06/01/2019', 101)
--)
;
MERGE #TRANSDETAIL_CTE AS TARGET USING
( SELECT Property , Account , Book , FinMonth , TranDate , TranType , TranID , TranDetailID , TranPerson , TranAmount , TranNumId , TranReference , TranNotes
FROM #TRANSDETAIL_TEMP
WHERE Property = 378
AND FinMonth >= convert(datetime, '06/01/2019', 101)
) AS SOURCE
ON ( TARGET.Property = SOURCE.Property
AND TARGET.Account = SOURCE.Account
AND TARGET.Book = SOURCE.Book
AND TARGET.FinMonth = SOURCE.FinMonth
AND TARGET.TranID = SOURCE.TranID
AND IsNull(target.TranDetailID, 0) = IsNull(SOURCE.TranDetailID , 0)
AND TARGET.TranAmount = SOURCE.TranAmount
AND IsNull(target.TranNotes,'') = IsNull(SOURCE.TranNotes,'')
)
WHEN MATCHED THEN UPDATE SET TARGET.TranDate = SOURCE.TranDate,
TARGET.TranType = SOURCE.TranType,
TARGET.TranPerson = SOURCE.TranPerson,
TARGET.TranNumId = SOURCE.TranNumId,
TARGET.TranReference = SOURCE.TranReference,
TARGET.TranNotes = SOURCE.TranNotes
WHEN NOT MATCHED THEN INSERT ( Property , Account , Book , FinMonth , TranDate , TranType , TranID , TranDetailID , TranPerson , TranAmount , TranNumId , TranReference , TranNotes )
VALUES ( SOURCE.Property, SOURCE.Account, SOURCE.Book, SOURCE.FinMonth, SOURCE.TranDate, SOURCE.TranType, SOURCE.TranID, SOURCE.TranDetailID, SOURCE.TranPerson, SOURCE.TranAmount, SOURCE.TranNumId , SOURCE.TranReference, SOURCE.TranNotes )
WHEN NOT MATCHED BY SOURCE THEN DELETE;
DROP TABLE #TRANSDETAIL_CTE;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 5, 2019 at 11:55 am
If you want to MERGE into table TRANSDETAIL, then that would be the TARGET.
Is the CTE meant to be the source of data that your MERGE is using? Then it would appear somewhere inside the USING clause.
If your temp table is the source of data for your MERGE, you don't need the CTE.
August 5, 2019 at 12:18 pm
If your temp table is the source of data for your MERGE, you don't need the CTE.
You would if you only wanted to affect a subset of TRANSDETAIL
Otherwise the 'NOT MATCHED BY SOURCE' would delete ALL non matching data
Far away is close at hand in the images of elsewhere.
Anon.
August 5, 2019 at 12:49 pm
I haven't used the MERGE statement since I got burned many years ago doing a
WHEN NOT MATCHED BY SOURCE THEN DELETE
As it turns out, you end up deleting records that you were not expecting to delete. The solution was to use a CTE as the source, which limited the records that could be deleted.
August 5, 2019 at 2:04 pm
I haven't used the MERGE statement since I got burned many years ago doing a
WHEN NOT MATCHED BY SOURCE THEN DELETEAs it turns out, you end up deleting records that you were not expecting to delete. The solution was to use a CTE as the source, which limited the records that could be deleted.
I've never been burned by MERGE because I don't use it. There was so much bad press about it not working correctly that I just kept writing the normal UPSERT type of code (Separate Insert, Update, and Delete) statements. I don't know that I'll ever trust merge.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2019 at 7:19 pm
So here is a quick sample of how the DELETE can have unexpected results.
First, the data to work with
IF OBJECT_ID(N'tempdb..#DestTable', N'U') IS NOT NULL
BEGIN
DROP TABLE #DestTable;
END;
GO
CREATE TABLE #DestTable (
CustID int NOT NULL
, InvDate date NOT NULL
, InvTotal decimal(18,2) NOT NULL
, PRIMARY KEY CLUSTERED (CustID, InvDate)
);
GO
IF OBJECT_ID(N'tempdb..#SourceTable', N'U') IS NOT NULL
BEGIN
DROP TABLE #SourceTable;
END;
GO
CREATE TABLE #SourceTable (
CustID int NOT NULL
, InvDate date NOT NULL
, InvTotal decimal(18,2) NOT NULL
, PRIMARY KEY CLUSTERED (CustID, InvDate)
);
GO
INSERT INTO #DestTable ( CustID, InvDate, InvTotal )
VALUES ( 10, '2019-02-25', 850.00 )
, ( 11, '2019-02-25', 420.00 )
, ( 11, '2019-03-25', 370.00 )
, ( 12, '2019-02-25', 650.00 )
, ( 12, '2019-03-25', 950.00 );
GO
INSERT INTO #SourceTable ( CustID, InvDate, InvTotal )
VALUES ( 10, '2019-03-25', 870.00 )
, ( 11, '2019-03-25', 450.00 );
GO
Now, lets do an UPDATE, INSERT and DELETE
-- We want to upsert the March values
MERGE INTO #DestTable AS Tgt
USING #SourceTable AS Src
ON Tgt.CustID = Src.CustID
AND Tgt.InvDate = Src.InvDate
WHEN MATCHED THEN -- UPDATE the Target -- Expect to UPDATE CustID=11 InvTotal from 370.00 to 450.00
UPDATE SET Tgt.InvTotal = Src.InvTotal
WHEN NOT MATCHED THEN -- INSERT into the Target -- Expect to INSERT CustID=10 InvTotal = 870
INSERT ( CustID, InvDate, InvTotal )
VALUES ( Src.CustID, Src.InvDate, Src.InvTotal )
WHEN NOT MATCHED BY SOURCE THEN -- DELETE from the Target -- Expect to DELETE CustID=12 WHERE InvDate = '2019-03-25'
DELETE;
So, what are the results?
SELECT *
FROM #DestTable;
CustID | InvDate | InvTotal
10 | 2019-03-25 | 870.00
11 | 2019-03-25 | 450.00
WELL, that didn't go as planned.
Repopulate the #Tables, and replace #DestTable with a CTE that filters #DestTable to only return March data.
-- We want to upsert the March values
WITH cteMarchValues AS (
SELECT d.*
FROM #DestTable AS d
WHERE d.InvDate = '2019-03-25'
)
MERGE INTO cteMarchValues AS Tgt
USING #SourceTable AS Src
ON Tgt.CustID = Src.CustID
AND Tgt.InvDate = Src.InvDate
WHEN MATCHED THEN -- UPDATE the Target -- Expect to UPDATE CustID=11 InvTotal from 370.00 to 450.00
UPDATE SET Tgt.InvTotal = Src.InvTotal
WHEN NOT MATCHED THEN -- INSERT into the Target -- Expect to INSERT CustID=10 InvTotal = 870
INSERT ( CustID, InvDate, InvTotal )
VALUES ( Src.CustID, Src.InvDate, Src.InvTotal )
WHEN NOT MATCHED BY SOURCE THEN -- DELETE from the Target -- Expect to DELETE CustID=12 WHERE InvDate = '2019-03-25'
DELETE;
Now we get the expected results
SELECT *
FROM #DestTable;
CustID | InvDate | InvTotal
10 | 2019-02-25 | 850.00
10 | 2019-03-25 | 870.00
11 | 2019-02-25 | 420.00
11 | 2019-03-25 | 450.00
12 | 2019-02-25 | 650.00
August 5, 2019 at 8:32 pm
DesNorton wrote:I haven't used the MERGE statement since I got burned many years ago doing a
WHEN NOT MATCHED BY SOURCE THEN DELETEAs it turns out, you end up deleting records that you were not expecting to delete. The solution was to use a CTE as the source, which limited the records that could be deleted.
I've never been burned by MERGE because I don't use it. There was so much bad press about it not working correctly that I just kept writing the normal UPSERT type of code (Separate Insert, Update, and Delete) statements. I don't know that I'll ever trust merge.
I'm trusting the MERGE, so far no issues, but I sure wouldn't use the DELETE part, I only insert or update.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 18, 2019 at 2:53 pm
Thanks to all of you who have taken the time to share your thoughts on this. To be clear this code is not something I have any control over, its coming from the software accounting app we use. I'm just trying to understand what its trying to do because I've never seen a a MERGE statement use a CTE as an alias for the table that you want to be the TARGET of the update/merge/delete. I haven't tested this code to verify it actually is making any changes I just assume it is. Based on the action you take in the application that calls this code its supposed to rebuild (recalculate and update) the values in the table TRANSDETAIL .
I know this is an old post now but in case any of you are still monitoring, if I read all your replies correctly then the code as shown is not doing anything with the data in the table TRANSDETAIL , is that correct?
I could test this by editing the values in the TRANSDETAIL table and executing the same function in the app that called this MERGE statement last time and see what the data in the TRANSDETAIL table is like afterward.
Kindest Regards,
Just say No to Facebook!October 18, 2019 at 4:39 pm
below86 is wrong about how the CTE works. A CTE is not a temp table, but rather a temp view, and (just like a view) you can update the underlying table under certain conditions. Here is a simple demo below.
DECLARE @Test TABLE (val TINYINT);
INSERT @Test (val)
VALUES(1), (2), (3), (4), (5);
WITH CTE AS
(
SELECT val
FROM @Test AS t
WHERE t.val < 4
)
MERGE CTE AS tgt
USING ( VALUES(1, 10), (2, 20), (3, 15), (4, 12) ) src(val, multiplier)
ON tgt.val = src.val
WHEN MATCHED THEN UPDATE SET tgt.val *= src.multiplier
WHEN NOT MATCHED BY SOURCE THEN DELETE;
SELECT t.val
FROM @Test AS t
Note that the 4 is not updated even though it's in the source, because it's filtered out by the CTE, and that 5 is not deleted even though it doesn't match the source, because it is also filtered out by the CTE.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 18, 2019 at 6:28 pm
Well, there is no way in hell I would have thought this would have updated the table. But Drew proved me wrong, well done sir. Learned something new today. Still wouldn't use the DELETE with the MERGE.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply