Prev: Using UPDATE to sequentially abbreviate address information
Next: design issues with large amounts of data
From: jefftyzzer on 3 Jul 2008 18:58
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
From: Plamen Ratchev on 3 Jul 2008 19:47
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
From: Serge Rielau on 4 Jul 2008 07:30
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
From: Dan Guzman on 5 Jul 2008 12:50
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(a)sbcglobal.net> wrote in message
news:8cb6709d-049a-40f1-96ea-e3530201063e(a)u12g2000prd.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

 | 
Pages: 1
Prev: Using UPDATE to sequentially abbreviate address information
Next: design issues with large amounts of data