Edit Question - KnowledgeHub

Title Body

I have a table with some persistent data in it. Now when I query it, I also have a pretty complex CTE which computes the values required for the result and I need to insert missing rows into the persistent table. In the end I want to select the result consisting of all the rows identified by the CTE but with the data from the table if they were already in the table, and I need the information whether a row has been just inserted or not. Simplified this works like this (the following code runs as a normal query if you like to try it): -- Set-up of test data, this would be the persisted table DECLARE @target TABLE (id int NOT NULL PRIMARY KEY) ; INSERT INTO @target (id) SELECT v.id FROM (VALUES (1), (2)) v(id); -- START OF THE CODE IN QUESTION -- The result table variable (will be several columns in the end) DECLARE @result TABLE (id int NOT NULL, new bit NOT NULL) ; WITH Source AS ( -- Imagine a fairly expensive, recursive CTE here SELECT * FROM (VALUES (1), (3)) AS Source (id) ) MERGE INTO @target AS Target USING Source ON Target.id = Source.id -- Perform a no-op on the match to get the output record WHEN MATCHED THEN UPDATE SET Target.id=Target.id WHEN NOT MATCHED BY TARGET THEN INSERT (id) VALUES (SOURCE.id) -- select the data to be returned - will be more columns OUTPUT source.id, CASE WHEN $action='INSERT' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END INTO @result ; -- Select the result SELECT * FROM @result; I don't like the WHEN MATCHED THEN UPDATE part, I'd rather leave the redundant update away but then I don't get the result row in the OUTPUT clause. Is this the most efficient way to do this kind of completing and returning data? Or would there be a more efficient solution without MERGE , for instance by pre-computing the result with a SELECT and then perform an INSERT of

Tags (comma-separated) Cancel