How this merge statement is working

Question

The sample code taken from https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

see the code

    MERGE Products AS TARGET
    USING UpdatedProducts AS SOURCE 
    ON (TARGET.ProductID = SOURCE.ProductID) 
    --When records are matched, update the records if there is any change
    WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate 
    THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate 
    --When no records are matched, insert the incoming records from source table to target table
    WHEN NOT MATCHED BY TARGET 
    THEN INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
    --When there is a row that exists in target and same record does not exist in source then delete this record target
    WHEN NOT MATCHED BY SOURCE 
    THEN DELETE 
    --$action specifies a column of type nvarchar(10) in the OUTPUT clause that returns 
    --one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE' according to the action that was performed on that row
    OUTPUT $action, 
    DELETED.ProductID AS TargetProductID, 
    DELETED.ProductName AS TargetProductName, 
    DELETED.Rate AS TargetRate, 
    INSERTED.ProductID AS SourceProductID, 
    INSERTED.ProductName AS SourceProductName, 
    INSERTED.Rate AS SourceRate; 

This portion code not clear.

OUTPUT $action, 
DELETED.ProductID AS TargetProductID, 
DELETED.ProductName AS TargetProductName, 
DELETED.Rate AS TargetRate, 
INSERTED.ProductID AS SourceProductID, 
INSERTED.ProductName AS SourceProductName, 
INSERTED.Rate AS SourceRate; 

what the above code is doing ? is it storing data into something or simply showing deleted data details ?

please guide me. thanks

Answer 1

Hi @T.Zacks ,

The OUTPUT clause was introduced in SQL Server 2005 version. The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE or DELETE statements. It even supports with a MERGE statement, which was introduced in SQL Server 2008 version.

The OUTPUT clause gives access to two virtual tables (Magic Tables). These are

“INSERTED” contains the new rows (INSERT or UPDATE‘s SET)
“DELETED” contains the old copy of the rows(UPDATE‘s SET)

Implement the OUTPUT Clause in the MERGE Statements
The OUTPUT clause within the MERGE statement will have access to both INSERTED and DELETED internal tables. That means both these tables are accessible at the same time while executing the MERGE statement.

As listed below, MERGE can handle up to these three below conditions while merging data from source to target table. We can use the OUTPUT clause in any of the below conditions within a MERGE statement.

Another advantage of using OUTPUT clause with in MERGE statement is that, we can use only one OUTPUT clause and capture all the rows that are effected within above three conditions. The result set from the OUTPUT clause can be filtered further using $action variable value of an action type DELETE or UPDATA or INSERT.

Please refer to a simple example:

IF OBJECT_ID('Department_Source', 'U') IS NOT NULL  
   DROP TABLE dbo.Department_Source;  
IF OBJECT_ID('Department_Target', 'U') IS NOT NULL  
   DROP TABLE dbo.Department_Target;  
CREATE TABLE [dbo].[Department_Source]  
(  
   [DepartmentID] [SMALLINT] NOT NULL,  
   [Name] VARCHAR(50) NOT NULL,  
   [GroupName] VARCHAR(50) NOT NULL,  
   [ModifiedDate] [DATETIME] NOT NULL  
) ON [PRIMARY];  
GO  
CREATE TABLE [dbo].[Department_Target]  
(  
   [DepartmentID] [SMALLINT] NOT NULL,  
   [Name] VARCHAR(50) NOT NULL,  
   [GroupName] VARCHAR(50) NOT NULL,  
   [ModifiedDate] [DATETIME] NOT NULL  
) ON [PRIMARY];  
GO  
  
---Insert some test values  
INSERT INTO [dbo].[Department_Source]  
(  
   [DepartmentID],  
   [Name],  
   [GroupName],  
   [ModifiedDate]  
)  
VALUES  
(  
   1, 'Engineering', 'Research and Development', GETDATE()  
);  
---Checking the Source Table Data  
SELECT  * FROM  [Department_Source];  
  
---Checking the Source Table Data  
SELECT  * FROM  [Department_Source];  
  
-----Inseting data when no macth found.  
MERGE [dbo].[Department_Target] AS tar  
USING [dbo].[Department_Source] AS src  
ON tar.[DepartmentID] = src.[DepartmentID]  
WHEN NOT MATCHED THEN  
   INSERT  
   (  
      [DepartmentID],  
      [Name],  
      [GroupName],  
      [ModifiedDate]  
   )  
   VALUES  
   (  
      src.[DepartmentID], src.[Name], src.[GroupName], src.[ModifiedDate]  
   )  
OUTPUT  
   $action,  
   inserted.*,  
   deleted.*;  

Output:

As this MERGE condition inserts data into the target table when there is no match in the table, we can only see the values from the inserted table. Only the inserted internal table was populated with the new records that are inserted to target within the MERGE statement. All the column values in the deleted table are NULL.

We see that the value stored in the $action variable is INSERT. This lets us know the MERGE statement is inserting data only.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

It is returning what the MERGE command actually changed.

Answer 3

Hi，@T.Zacks
Output_clause>
Returns a row for every row in target_table that's updated, inserted, or deleted, in no particular order. $action can be specified in the output clause. $action is a column of type nvarchar(10) that returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action done on that row. The OUTPUT clause is the recommended way to query or count rows affected by a MERGE. For more information about the arguments and behavior of this clause, see OUTPUT Clause (Transact-SQL).

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.