Sql server® 2012 t-sql fundamentals

282  

Microsoft SQL Server 2012 T-SQL Fundamentals

DELETE

 with 

OUTPUT

The next example demonstrates the use of the OUTPUT clause with a DELETE statement. First, run the 

following code to create a copy of the Orders table from the Sales schema in the dbo schema.

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders; 

 

CREATE TABLE dbo.Orders 

( 

  orderid        INT          NOT NULL, 

  custid         INT          NULL, 

  empid          INT          NOT NULL, 

  orderdate      DATETIME     NOT NULL, 

  requireddate   DATETIME     NOT NULL, 

  shippeddate    DATETIME     NULL, 

  shipperid      INT          NOT NULL, 

  freight        MONEY        NOT NULL 

    CONSTRAINT DFT_Orders_freight DEFAULT(0), 

  shipname       NVARCHAR(40) NOT NULL, 

  shipaddress    NVARCHAR(60) NOT NULL, 

  shipcity       NVARCHAR(15) NOT NULL, 

  shipregion     NVARCHAR(15) NULL, 

  shippostalcode NVARCHAR(10) NULL, 

  shipcountry    NVARCHAR(15) NOT NULL, 

  CONSTRAINT PK_Orders PRIMARY KEY(orderid) 

); 

GO 

 

INSERT INTO dbo.Orders SELECT * FROM Sales.Orders;

The following code deletes all orders that were placed prior to 2008 and, using the OUTPUT 

clause, returns attributes from the deleted rows.

DELETE FROM dbo.Orders 

  OUTPUT 

    deleted.orderid, 

    deleted.orderdate, 

    deleted.empid, 

    deleted.custid 

WHERE orderdate < '20080101';

This DELETE statement returns the following result set.

orderid     orderdate                 empid       custid 

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

10248       2006-07-04 00:00:00.000   5           85 

10249       2006-07-05 00:00:00.000   6           79 

10250       2006-07-08 00:00:00.000   4           34 

10251       2006-07-08 00:00:00.000   3           84 

10252       2006-07-09 00:00:00.000   4           76 

... 

10400       2007-01-01 00:00:00.000   1           19 

10401       2007-01-01 00:00:00.000   1           65 

10402       2007-01-02 00:00:00.000   8           20 

www.it-ebooks.info

---