OUTPUT Clause (Composable DML) in SQL Server 2008

Microsoft introduce OUTPUT clause with SQL Server 2005. The OUTPUT clause made it possible to get the information updates resulted from any DML statement executed.

There are few updates using the OUTPUT clause in SQL Server 2008. But before that for those of you who are not acquainted with this OUTPUT clause, I want to discuss a little about this clause.

CREATE TABLE CUSTOMER_TBL
(
CustomerID int PRIMARY KEY IDENTITY,
CustomerName varchar(500)
)


Declare @Mytable table (Name varchar(50))
INSERT INTO CUSTOMER_TBL (CustomerName)
OUTPUT inserted.CustomerName as Naam into @Mytable
VALUES ('New Customer')

SELECT * FROM @Mytable
SELECT Name FROM @Mytable GROUP BY Name

With SQL Server 2008's row constructor, this statement can be used as follows:

Declare @Mytable table (Name varchar(50))

INSERT INTO CUSTOMER_TBL (CustomerName)
OUTPUT inserted.CustomerName as Naam into @Mytable
VALUES
('New Customer'),
('Another New Customer'),
('Third Customer'),
('New Customer')

SELECT * FROM @Mytable
SELECT Name FROM @Mytable GROUP BY Name

With 2008, this OUTPUT clause can do miracles because of composable DML feature provided. Let us define a new table :
CREATE TABLE CUSTOMER_ORDERS
(
CustomerID int PRIMARY KEY IDENTITY,
OrderDetail varchar(200),
CustomerName varchar(500)
)


INSERT INTO CUSTOMER_ORDERS(CustomerName)
Select e.CustName from
(
INSERT INTO CUSTOMER_TBL (CustomerName)
OUTPUT inserted.CustomerName as CustName
VALUES('NewCustomer'),
('Another New Customer'),
('Third Customer'),
('New Customer')
)e


In the above statement, all the new Customer Names inserted as part of the inner INSERT statement are used in the above INSERT statement. Any of these statements may be INSERT, UPDATE or DELETE. If you start the SQL Server Profiler trace then you would certainly admire that it is still an atomic operation.