Systems Engineering and RDBMS

SQL Server 2008: INSERT over a DML statement

Posted by decipherinfosys on November 28, 2007

In this post, we are going to cover another T-SQL enhancement in SQL Server 2008. We will be using the same set of tables that we did in one of our previous blog post in which we had talked about the MERGE command in SQL Server 2008.

Using the same set of tables, here is how the data looks like:

CREATE TABLE PRODUCT_CATALOG (PRODUCT_NAME NVARCHAR(100) NOT NULL, QTY INT NOT NULL CHECK (QTY > 0));
CREATE TABLE PRODUCT_SALE (PRODUCT_NAME NVARCHAR(100) NOT NULL, SALE_QTY INT NOT NULL);

INSERT INTO PRODUCT_CATALOG (PRODUCT_NAME, QTY) VALUES (‘TOY1’, 100);
INSERT INTO PRODUCT_CATALOG (PRODUCT_NAME, QTY) VALUES (‘TOY2’, 50);
INSERT INTO PRODUCT_SALE (PRODUCT_NAME, SALE_QTY) VALUES (‘TOY1’, 100);
INSERT INTO PRODUCT_SALE (PRODUCT_NAME, SALE_QTY) VALUES (‘TOY2’, 10);
INSERT INTO PRODUCT_SALE (PRODUCT_NAME, SALE_QTY) VALUES (‘TOY3’, 500);
GO

If you read that post, you will see that we had used the MERGE command to do the DML (Delete, Insert, Update) logic on the PRODUCT_CATALOG table. This was the first example of the MERGE command that we had used in that post:

MERGE PRODUCT_CATALOG PC /* target table */
USING PRODUCT_SALE PS /* source table */
ON PC.PRODUCT_NAME = PS.PRODUCT_NAME
WHEN MATCHED AND (Qty – SALE_QTY = 0) THEN
DELETE
WHEN MATCHED THEN
/* update stock if you still hold some stock*/
UPDATE SET Qty = Qty – SALE_QTY
WHEN NOT MATCHED THEN
/*insert a row if the stock is newly acquired*/
INSERT VALUES (PRODUCT_NAME, SALE_QTY)
/* output details of INSERT/UPDATE/DELETE operations
– made on the target table*/
OUTPUT $action, inserted.PRODUCT_NAME, inserted.QTY, deleted.PRODUCT_NAME, deleted.QTY;

Now, suppose that you want the output of this to be put into a separate audit or processing table but only when the update is done or when a certain other condition is met. It is possible to be able to now to a select atop the MERGE command. We will first need to create a table for storing that data.

CREATE TABLE TEST_INSERT_OVER_DML (ACTION_CODE NVARCHAR(50), I_PRODUCT_NAME NVARCHAR(100), I_QTY INT, D_PRODUCT_NAME NVARCHAR(100), D_QTY INT)
GO

/*************************************************
And now the insert over DML statement
**************************************************/
INSERT INTO TEST_INSERT_OVER_DML (ACTION_CODE, I_PRODUCT_NAME, I_QTY, D_PRODUCT_NAME, D_QTY)
select Action_Code, Inserted_Product_Name, Inserted_QTY, Deleted_Product_Name, Deleted_Qty
from
(
MERGE PRODUCT_CATALOG PC /* target table */
USING PRODUCT_SALE PS /* source table */
ON PC.PRODUCT_NAME = PS.PRODUCT_NAME
WHEN MATCHED AND (Qty – SALE_QTY = 0) THEN
DELETE
WHEN MATCHED THEN
/* update QTY if you still hold some QTY for that PRODUCT*/
UPDATE SET Qty = Qty – SALE_QTY
WHEN NOT MATCHED THEN
/*insert a row if the PRODUCT is newly acquired*/
INSERT VALUES (PRODUCT_NAME, SALE_QTY)
/* output details of INSERT/UPDATE/DELETE operations
– made on the target table*/
OUTPUT $action, inserted.PRODUCT_NAME, inserted.QTY, deleted.PRODUCT_NAME, deleted.QTY
) Change (Action_Code, Inserted_Product_Name, Inserted_QTY, Deleted_Product_Name, Deleted_Qty);

And after executing it, let’s take a look at the data in the PRODUCT_CATALOG and the TEST_INSERT_OVER_DML tables:

/*************************

 PRODUCT_CATALOG

**************************/

PRODUCT_NAME                                                                                         QTY

---------------------------------------------------------------------------------------------------- -----------

TOY2                                                                                                 40

TOY3                                                                                                 500
/*************************

 TEST_INSERT_OVER_DML

**************************/

ACTION_CODE I_PRODUCT_NAME   I_QTY       D_PRODUCT_NAME  D_QTY

----------- --------------   ----------- --------------- -----------

INSERT      TOY3             500         NULL            NULL

DELETE      NULL             NULL        TOY1            100

UPDATE      TOY2             40          TOY2            50

As you can see from above, the action history log has been captured and the DELETE, INSERT and UPDATE actions took place on the PRODUCT_CATALOG table since the product: TOY1 got deleted, the QTY for TOY2 got decremented by 10 and TOY3 record got added.

This new feature will help a lot in data warehousing ETLM operations and also for preparing audit logs for DML operations though CDC (Change Data Capture) would be a better option for auditing.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: