Using the OUTPUT and OUTPUT INTO clauses in SQL Server 2005

SQL Server 2005 introduced the OUTPUT & OUTPUT INTO clauses.  Prior to SQL 2005, one had to use 2 SQL statements in a batch to get the values that were just inserted/updated/deleted by the DML statement.  These can now be done using a single statement with the help of these clauses.  These can be very useful for logging/auditing purposes, for confirmation messages or general application processing.

Let’s look at one of such scenarios where we need to use it for error logging for interfaces into the application.  Say, you have 100,000 records in one of the interface tables and out of those 1200 failed some sort of a validation.  You now need to log those as errors in the logging table and instead of doing it row by row, you want to do the right thing and write a SET based code.  Here is an example illustrating such a scenario:

Say the interface table is INPT_PO_HDR which stores the purchase orders that you get from the source with which you are integrating with.  Now, suppose that for a given load, they sent an XML file or a flat file with the data that our program loads up into the table.  If the table now has say 100,000 records for that given load, we want to process them in a SET based fashion so that performance is optimal.  We know that we have 4 validations that we need to do on this incoming data set prior to loading them in our transactional system of tables.  Say one of the validations finds that 1200 records fail the validation check, another one finds that 10 records fail the validation check and the remaining 2 validations go through fine.

Under such a scenario, the code is required to log the error records into the logging table (let’s call it MESSAGE_LOG) and also update the interface table’s ERROR_NBR column with the ID value from the MESSAGE_LOG table so that proper inquiries can be done against it.  Depending upon whether the number of records are going to be large or small, we can decide to use a table variable or a temp table for this purpose (we had covered these in our blog posts before).

/*********************************************************************************************************
Create the temp table first
**********************************************************************************************************/
create table #ERROR_ID_LIST     (inpt_po_hdr_id int, msg_log_id int);

/*********************************************************************************************************
Let’s suppose the country code validation failed
Get the records that failed
**********************************************************************************************************/

For the records that fail the country_code validation, our aim is to log these into the MESSAGE_LOG table as well as tie them up with the INPT_PO_HDR.  In SQL Server 2005, using the “OUTPUT clause”, we can insert these records in the MESSAGE_LOG table and get back the list of records in order to go back and do the updates to the interface table.

/**********************************************************************************************************
INSERT ANY ERROR RECORDS INTO THE MESSAGE_LOG TABLE
***********************************************************************************************************/

INSERT INTO MESSAGE_LOG (….)
OUTPUT INSERTED.MSG_LOG_ID, A.inpt_po_hdr_id
INTO #ERROR_ID_LIST
SELECT ….
from inpt_po_hdr
where stat_code = 0
and not exists (select 1 from country where country_code = inpt_po_hdr.country_code);

update INPT_PO_HDR
set     ERROR_NBR = B.MSG_LOG_ID,
STAT_CODE = 90            /*Error Status*/
from INPT_PO_HDR A, #ERROR_ID_LIST AS B
WHERE A.INPT_PO_HDR_ID = B.INPT_PO_HDR_ID

Now, one can easily join the two tables to look at the error messages as well as the values that were sent in and take appropriate action.

SELECT
<col_list>
from INPT_PO_HDR AS A
INNER JOIN MESSAGE_LOG AS B
ON A.ERROR_NBR = B.MSG_LOG_ID
WHERE A.STAT_CODE = 90
AND… <additional filter criteria – maybe datetime ranges>