SELECTing from a DML statement

[jefftyzzer]

DB2 allows one to select from an insert, update, or delete, e.g.:

select * from old table (delete from x where y = 1);

--OR--

select * from new table (update x set y = 1 where z = 2);

--OR--

select * from final table (insert into x (c1) values (1));

Is there any equivalent in SQL Server?

Thanks and regards,

--Jeff

[Plamen Ratchev]

On SQL Server 2008 you can use composable DML:

SELECT keycol, datacol
FROM (DELETE Foo
OUTPUT Deleted.keycol, Deleted.datacol
WHERE keycol = 1) AS T(keycol, datacol);

The key here is the OUTPUT clause that provides the data set.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Serge Rielau]

Plamen Ratchev wrote:

On SQL Server 2008 you can use composable DML:

SELECT keycol, datacol
FROM (DELETE Foo
OUTPUT Deleted.keycol, Deleted.datacol
WHERE keycol = 1) AS T(keycol, datacol);

The key here is the OUTPUT clause that provides the data set.

Nice! Alway glad to see my stuff used. :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Dan Guzman]

To add on to Planen's response, you can also use the OUTPUT keyword in SQL
2005 or SQL 2008 to return DML results. For example:

DELETE dbo.Foo
OUTPUT deleted.keycol, deleted.datacol
WHERE keycol = 1;

This isn't as flexible as the composable DML introduced in SQL 2008 but I
believe it addresses your stated requirements. See the Books Online for
more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"jefftyzzer" <je********@sbcglobal.netwrote in message
news:8c**********************************@u12g2000 prd.googlegroups.com...

DB2 allows one to select from an insert, update, or delete, e.g.:

select * from old table (delete from x where y = 1);

--OR--

select * from new table (update x set y = 1 where z = 2);

--OR--

select * from final table (insert into x (c1) values (1));

Is there any equivalent in SQL Server?

Thanks and regards,

--Jeff