Sql server® 2012 t-sql fundamentals

 

CHAPTER 8

 

Data Modification

 

 283

10403       2007-01-03 00:00:00.000   4           20 

10404       2007-01-03 00:00:00.000   2           49 

... 

 

(560 row(s) affected)

If you want to archive the rows that are deleted, simply add an INTO clause and specify the archive 

table name as the target.

UPDATE

 with 

OUTPUT

By using the OUTPUT clause with an UPDATE statement, you can refer to both the image of the modi-

fied row before the change (by prefixing the attribute names with the deleted keyword) and to the 

image after the change (by prefixing the attribute names with the inserted keyword). This way, you 

can return both old and new images of the updated attributes.

Before I demonstrate how to use the OUTPUT clause in an UPDATE statement, you should first run 

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

schema.

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

 

CREATE TABLE dbo.OrderDetails 

( 

  orderid   INT           NOT NULL, 

  productid INT           NOT NULL, 

  unitprice MONEY         NOT NULL 

    CONSTRAINT DFT_OrderDetails_unitprice DEFAULT(0), 

  qty       SMALLINT      NOT NULL 

    CONSTRAINT DFT_OrderDetails_qty DEFAULT(1), 

  discount  NUMERIC(4, 3) NOT NULL 

    CONSTRAINT DFT_OrderDetails_discount DEFAULT(0), 

  CONSTRAINT PK_OrderDetails PRIMARY KEY(orderid, productid), 

  CONSTRAINT CHK_discount  CHECK (discount BETWEEN 0 AND 1), 

  CONSTRAINT CHK_qty  CHECK (qty > 0), 

  CONSTRAINT CHK_unitprice CHECK (unitprice >= 0) 

); 

GO 

 

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

The following UPDATE statement increases the discount of all order details for product 51 by 5 

percent and uses the OUTPUT clause to return the product ID, old discount, and new discount from 

the modified rows.

UPDATE dbo.OrderDetails 

  SET discount += 0.05 

OUTPUT 

  inserted.productid, 

  deleted.discount AS olddiscount, 

  inserted.discount AS newdiscount 

WHERE productid = 51;

www.it-ebooks.info

---

284  

Microsoft SQL Server 2012 T-SQL Fundamentals

This statement returns the following output.

productid   olddiscount  newdiscount 

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

51          0.000        0.050 

51          0.150        0.200 

51          0.100        0.150 

51          0.200        0.250 

51          0.000        0.050 

51          0.150        0.200 

51          0.000        0.050 

51          0.000        0.050 

51          0.000        0.050 

51          0.000        0.050 

... 

 

(39 row(s) affected)