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]

Re: SELECTing from a DML statement

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]

Re: SELECTing from a DML statement

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]

Re: SELECTing from a DML statement

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 " <jefftyzzer@sbc global.netwrote in message
news:8cb6709d-049a-40f1-96ea-e3530201063e@u1 2g2000prd.googl egroups.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