November CTP for SQL Server 2008 is now available

The November CTP is now available - you can download it from:

http://www.microsoft.com/downloads/details.aspx?FamilyId=3BF4C5CA-B905-4EBC-8901-1D4C1D1DA884&displaylang=en

Pretty exciting enhancements are there in this CTP - we will be blogging about those in the coming days.

MERGE command in SQL Server 2008

MERGE command (also known as UPSERT in Oracle circles) is nothing new to Oracle. It has existed since quite some time. We have also blogged about it on our site - including the enhancements to that command that were made in Oracle 10g. It gets introduced in SQL Server 2008 as well which is a welcome addition for all the data-warehousing/data-mining community as well as those writing complicated feeds to their OLTP systems. This command is way more powerful than just thinking of it as an UPDATE/DELETE/INSERT combined into one single statement. Let’s set up two tables and then start going through the features of this command to see how one can utilize it.

/**************************************************************************************************************
We are setting up two tables - One for the Product Catalog and the other one for the sales of the products
***************************************************************************************************************/
SET NOCOUNT ON
GO

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

/**************************************************************************************************************
Apply changes to the PRODUCT_CATALOG table based on daily sales
that are tracked in the incoming Product_Sale table from different stores.
Delete the record from the PRODUCT_CATALOG table if all the items for that Product_Name have been sold.
Update and decrement the quantity if quantity after the sale is not 0, and
Insert a new record if there was a new item introduced at one of the regional
stores.
***************************************************************************************************************/
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;

$action    PRODUCT_NAME    QTY   PRODUCT_NAME  QTY
 ---------- ------------- ------ ------------- ------
 INSERT     TOY3            500         NULL    NULL
 DELETE     NULL            NULL        TOY1    100
 UPDATE     TOY2            40          TOY2    50

SELECT * FROM PRODUCT_CATALOG;
GO

PRODUCT_NAME                                                                                         QTY
 ---------------------------------------------------------------------------------------------------- -----------
 TOY2                                                                                                 40
 TOY3                                                                                                 500

This is a very simple example to illustrate the functinality of the MERGE command. The comments given above are self explanatory. Let’s move on to other advanced features of this wonderful enhancement. In the example above, you saw that we are joining between two tables. There is no reason why we cannot join the PRODUCT_CATALOG table with another sub-query:

Suppose, we had another table called SALE_ORDER in which we kept track of the sales of all the products per store and the quantity that was sold. In that scenario, we will get the total Sale Quantity by summing it up over the PRODUCT_NAME for a given date.

CREATE TABLE SALE_ORDER (STORE_NBR INT NOT NULL, PRODUCT_NAME NVARCHAR(100) NOT NULL, SALE_QTY INT NOT NULL, ORDER_DATE DATETIME NOT NULL DEFAULT GETDATE());
INSERT INTO SALE_ORDER (STORE_NBR, PRODUCT_NAME, SALE_QTY) VALUES (1, ‘TOY1′, 10);
INSERT INTO SALE_ORDER (STORE_NBR, PRODUCT_NAME, SALE_QTY) VALUES (2, ‘TOY1′, 20);
INSERT INTO SALE_ORDER (STORE_NBR, PRODUCT_NAME, SALE_QTY) VALUES (3, ‘TOY1′, 50);
GO

And let us drop and re-create the two tables from above.

Please note that in the query below, we are

MERGE PRODUCT_CATALOG PC
USING (SELECT PS.PRODUCT_NAME, SUM(SO.SALE_QTY) AS SALE_QTY
FROM PRODUCT_SALE PS
INNER JOIN SALE_ORDER SO
ON SO.PRODUCT_NAME = PS.PRODUCT_NAME
WHERE SO.ORDER_DATE >= CONVERT(VARCHAR(10), GETDATE(), 101)
AND SO.ORDER_DATE < CONVERT(VARCHAR(10), GETDATE() + 1, 101)
GROUP BY PS.PRODUCT_NAME) AS IV (PRODUCT_NAME, SALE_QTY)
ON PC.PRODUCT_NAME = IV.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;

$action    PRODUCT_NAME  QTY         PRODUCT_NAME  QTY
 ---------- ------------- ----------- ------------- -----
 UPDATE     TOY1          20          TOY1          100

SELECT * FROM PRODUCT_CATALOG;
GO

PRODUCT_NAME QTY
—————————————————————————————————- ———–
TOY1 20
TOY2 50

In the next post, we will go over another T-SQL enhancement in SQL Server 2008 - in that one, we will cover the enhancement to the INSERT statement - one can insert over a DML and one can also have minimally logged insert operations (same as direct path inserts in the case of Oracle).