I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005 Output Clause

I ran into this little gem today while doing some deletes and forgeting Delete syntax :)))
There is now Output clause for DELETE, INSERT and UPDATE statements.
It outputs the affected rows into a table variable, a simple resultset or into a table.
I guess auditing is possible this way too now.

Example:
create table test(id int identity(1,1), name varchar(100))
insert into test (name)
select 'name 1' union all select 'name 2' union all select 'name 3' union all 
select 'name 4' union all select 'name 5' union all select 'name 6' union all 
select 'name 7' union all select 'name 8' union all select 'name 9' union all 
select 'name 10' union all select 'name 11' union all select 'name 12' 
go
select * from test
declare @deleted table (id int, name varchar(100))
delete test
output DELETED.* into @deleted
where id < 5
select * from @deleted
delete test
output DELETED.*
where id between 5 and 10
select * from test
go
drop table test
More info in BOL under OUTPUT Clause

Print | posted on Thursday, June 08, 2006 3:58 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server 2005 Output Clause

very cool ...I've done lots of reading about SQL 2005 and this is the first I've seen on this! Good find, this might be pretty handy ..
6/8/2006 4:53 PM | Jeff
Gravatar

# re: SQL Server 2005 Output Clause

yeah... this option can also come in handy when having to output inserted identity column values.
6/8/2006 5:52 PM | Andrej Tozon
Gravatar

# Interesting Finds: June 9, 2006 AM edition

6/9/2006 10:30 AM | Jason Haley
Gravatar

# re: SQL Server 2005 Output Clause

Hi
i am facing one problem how to delete duplicate rows from the tables
3/26/2008 2:57 PM | bahskar
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET