Notes on SQL

Recently I’ve been making a lot of use of the OUTPUT option for insert and update actions. From this work I’ve already posted about the behaviour of IDENTITY and suchlike.
Now I have looked at the behaviour of OUTPUT within the MERGE command.

Background

In my current role when a table has data inserted or updated (it is never deleted, but the process would be similar) there is also an entry made into a ‘history’ table, so it is possible to track what data was changed and who was responsible for that change.
The initial insert is written to this history table and any subsequent changes also have the ‘before’ details written to the history table.

Preparing the Example

First, create a very basic table for supplier details and a history table:

CREATE TABLE dbo.Supplier
(
    ID	INT IDENTITY(1,1),
	BusinessName	VARCHAR(50),
	Telephone	VARCHAR(20),
	PrimaryContact	VARCHAR(50)
);

CREATE TABLE dbo.Supplier_History
(
        ID              INT	IDENTITY(1,1),
	SupplierID	INT,
	BusinessName	VARCHAR(50),
	Telephone	VARCHAR(20),
	PrimaryContact	VARCHAR(50),
	ActionType	VARCHAR(10),
	DateTimeStamp	DATETIME DEFAULT GETDATE()
);
GO

The history table some additional columns –
it’s own ID column,
an ‘Actiontype‘ column, to show whether the data was entered as a result of an insert or update,
a ‘DateTimeStamp‘, to show when the action occurred.

Other columns could be added, such as the identity of the person who triggered this action but that isn’t required for this example.

Basic OUTPUT examples with INSERT/UPDATE

To insert a row into the Supplier table and insert a row into the history table:

INSERT INTO dbo.Supplier
		( BusinessName ,
		  Telephone ,
		  PrimaryContact )
	OUTPUT	Inserted.ID ,
		Inserted.BusinessName ,
		Inserted.Telephone ,
		Inserted.PrimaryContact ,
		'INSERT'
	INTO dbo.Supplier_History ( SupplierID, 
				    BusinessName, 
				    Telephone,
				    PrimaryContact, 
				    ActionType )
	VALUES
		( 'Widgets by Mail' ,
		  '7765 222' ,
		  'John Smith' );
GO

The OUTPUT/INTO has written the Inserted details into the history table, which will add the date and time that this happened:

Similarly, when an Update has the OUTPUT clause specified it can be used to insert the original values into the history table:

  UPDATE dbo.Supplier
  SET Telephone = '7765 333'
  OUTPUT Deleted.ID ,
	 Deleted.BusinessName ,
	 Deleted.Telephone ,
	 Deleted.PrimaryContact,
	 'UPDATE'
  INTO dbo.Supplier_History(SupplierID, 
			    BusinessName, 
			    Telephone, 
			    PrimaryContact, 
			    ActionType)
  WHERE ID = 1;


  UPDATE dbo.Supplier
  SET PrimaryContact = 'Alan Jones'
  OUTPUT Deleted.ID ,
	 Deleted.BusinessName ,
	 Deleted.Telephone ,
	 Deleted.PrimaryContact,
	 'UPDATE'
  INTO dbo.Supplier_History(SupplierID, 
			    BusinessName, 
			    Telephone, 
			    PrimaryContact, 
			    ActionType)
  WHERE ID = 1;

  UPDATE dbo.Supplier
  SET BusinessName = 'Widgets by Post'
  OUTPUT Deleted.ID ,
	 Deleted.BusinessName ,
	 Deleted.Telephone ,
	 Deleted.PrimaryContact,
	'UPDATE'
  INTO dbo.Supplier_History(SupplierID, 
			    BusinessName, 
			    Telephone, 
			    PrimaryContact, 
			    ActionType)
  WHERE ID = 1;

And the history table has an entry for the data as it was before this update:

OUTPUT with the MERGE Command

The MERGE command allows data to be inserted, updated or deleted all within one statement. Because of this, OUTPUT behaves slightly differently. The OUTPUT appears only once, at the end of the MERGE statement. Because of this, a variable called ‘$Action’ is utilised (only available in MERGE) which will show whether the output action was taken as a result of insert, update or delete.
Examples I have seen of this usage usually show the output of both the Inserted and Deleted values to an output table. However, I require the Inserted or Deleted, depending upon the action taken.
It is possible to examine the value of $Action at the time of output, with a CASE statement. This means that I can select either Inserted values or Deleted values as appropriate.

Example of MERGE with OUTPUT and $ACTION

First of all, create a table with data to use for the merge:

CREATE TABLE dbo.Suppliers_Staging
	(
	  BusinessName		VARCHAR(50) NULL ,
	  Telephone		VARCHAR(20) NULL ,
	  PrimaryContact	VARCHAR(50) NULL
	); 

GO

INSERT INTO dbo.Suppliers_Staging
		( BusinessName ,
		  Telephone ,
		  PrimaryContact )
	VALUES
		( 'Widgets by Post' ,  '7765 333' , 'Fred Smith'),  --this should update the existing data
		( 'Any Old Iron', '555 9866', 'John Doe');	    --this should insert a new row of data

The code to execute the MERGE and OUTPUT:

MERGE dbo.Supplier AS s
USING dbo.Suppliers_Staging AS ss
ON ss.BusinessName = s.BusinessName
WHEN NOT MATCHED BY TARGET THEN
	INSERT ( BusinessName ,
			 Telephone ,
			 PrimaryContact )
	VALUES ( ss.BusinessName ,
			 ss.Telephone ,
			 ss.PrimaryContact )
WHEN MATCHED THEN
	UPDATE SET s.BusinessName = ss.BusinessName ,
				s.Telephone = ss.Telephone ,
				s.PrimaryContact = ss.PrimaryContact
OUTPUT CASE	WHEN $action = 'INSERT' THEN Inserted.ID
			ELSE Deleted.ID
	   END ,
	CASE WHEN $action = 'INSERT' THEN Inserted.BusinessName
		 ELSE Deleted.BusinessName
	END ,
	CASE WHEN $action = 'INSERT' THEN Inserted.Telephone
		 ELSE Deleted.Telephone
	END ,
	CASE WHEN $action = 'INSERT' THEN Inserted.PrimaryContact
		 ELSE Deleted.PrimaryContact
	END ,
	$action
	INTO dbo.Supplier_History ( SupplierID, BusinessName, Telephone,
								PrimaryContact, ActionType );

The result of this is two additional rows in the history table, one from the Inserted data and the other from the Deleted data. The $Action variable stored in ‘ActionType’ signifies which:

Further Reading

MS Documentation for MERGE
Rob Farley – MERGE Gives Better OUTPUT options
Using the OUTPUT Clause with T-SQL Merge