Prev: How to select day_of_week and/or date given a period?
Next: Blocking problem
From: SnapDive on 23 Feb 2010 16:10

Using SQL Server 2005, I would like to do a delete statement and
simply capture the number of rows affected by the delete to an int so
I can write that message to a global temp table.

Something like the below, but actually working. :)

-- ##globaltemp already exists
declare @counttable ( curr int )

delete from mytable where someid < 1000
select count (output deleted.* ) into @counttable
select 'deleted: ' + tt.curr into ##globaltemp from @counttable tt


How can I do this using outout instead of using ROWCOUNT ?

Thanks.
From: Gert-Jan Strik on 23 Feb 2010 16:20
What's wrong with using ROWCOUNT? Sound like you already have a
solution...

--
Gert-Jan


SnapDive wrote:
>
> Using SQL Server 2005, I would like to do a delete statement and
> simply capture the number of rows affected by the delete to an int so
> I can write that message to a global temp table.
>
> Something like the below, but actually working. :)
>
> -- ##globaltemp already exists
> declare @counttable ( curr int )
>
> delete from mytable where someid < 1000
> select count (output deleted.* ) into @counttable
> select 'deleted: ' + tt.curr into ##globaltemp from @counttable tt
>
> How can I do this using outout instead of using ROWCOUNT ?
>
> Thanks.
From: sloan on 23 Feb 2010 16:23
Using my example here:

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!519.entry


You could modify....and do something like the below

delete from dbo.EmployeeTable
output deleted.EmpKey , deleted.EmpAge, NULL , 'Employee Deleted' into
#SomeLocalTempTableYouDefine (EntityKey , OldValue , NewValue , Tag)
where EmpAge > 0;--Test multi rows


select count(*) from #SomeLocalTempTableYouDefine as DeleteCount
.........

I don't think you can get aggregate data from "output", but I'll let someone
smarter than me respond to that one.






"SnapDive" <SnapDive(a)community.nospam> wrote in message
news:0mg8o5d9tvp1pcpta1j9sb34t9d8cabu3i(a)4ax.com...
>
> Using SQL Server 2005, I would like to do a delete statement and
> simply capture the number of rows affected by the delete to an int so
> I can write that message to a global temp table.
>
> Something like the below, but actually working. :)
>
> -- ##globaltemp already exists
> declare @counttable ( curr int )
>
> delete from mytable where someid < 1000
> select count (output deleted.* ) into @counttable
> select 'deleted: ' + tt.curr into ##globaltemp from @counttable tt
>
>
> How can I do this using outout instead of using ROWCOUNT ?
>
> Thanks.


From: Tom Cooper on 24 Feb 2010 00:57
I don't think I'm smarter than you you are, but BOL is smarter than me and
BOL says you can't use aggregate functions.

Tom

"sloan" <sloan(a)ipass.net> wrote in message
news:%237CG%235MtKHA.6124(a)TK2MSFTNGP04.phx.gbl...
> Using my example here:
>
> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!519.entry
>
>
> You could modify....and do something like the below
>
> delete from dbo.EmployeeTable
> output deleted.EmpKey , deleted.EmpAge, NULL , 'Employee Deleted' into
> #SomeLocalTempTableYouDefine (EntityKey , OldValue , NewValue , Tag)
> where EmpAge > 0;--Test multi rows
>
>
> select count(*) from #SomeLocalTempTableYouDefine as DeleteCount
> ........
>
> I don't think you can get aggregate data from "output", but I'll let
> someone smarter than me respond to that one.
>
>
>
>
>
>
> "SnapDive" <SnapDive(a)community.nospam> wrote in message
> news:0mg8o5d9tvp1pcpta1j9sb34t9d8cabu3i(a)4ax.com...
>>
>> Using SQL Server 2005, I would like to do a delete statement and
>> simply capture the number of rows affected by the delete to an int so
>> I can write that message to a global temp table.
>>
>> Something like the below, but actually working. :)
>>
>> -- ##globaltemp already exists
>> declare @counttable ( curr int )
>>
>> delete from mytable where someid < 1000
>> select count (output deleted.* ) into @counttable
>> select 'deleted: ' + tt.curr into ##globaltemp from @counttable tt
>>
>>
>> How can I do this using outout instead of using ROWCOUNT ?
>>
>> Thanks.
>
>

From: Jay on 24 Feb 2010 01:28
I am confused as to why you think @@ROWCOUNT isn't the best solution?

DELETE FROM mytable WHERE someid < 1000;
SET @CurDeleted = @@ROWCOUNT;
etc.

Seems way too simple to pass up.

"SnapDive" <SnapDive(a)community.nospam> wrote in message
news:0mg8o5d9tvp1pcpta1j9sb34t9d8cabu3i(a)4ax.com...
>
> Using SQL Server 2005, I would like to do a delete statement and
> simply capture the number of rows affected by the delete to an int so
> I can write that message to a global temp table.
>
> Something like the below, but actually working. :)
>
> -- ##globaltemp already exists
> declare @counttable ( curr int )
>
> delete from mytable where someid < 1000
> select count (output deleted.* ) into @counttable
> select 'deleted: ' + tt.curr into ##globaltemp from @counttable tt
>
>
> How can I do this using outout instead of using ROWCOUNT ?
>
> Thanks.


 |  Next  |  Last
Pages: 1 2
Prev: How to select day_of_week and/or date given a period?
Next: Blocking problem