Rows affected count from bulk insert select.

[Jason Finch <jf...@emergency.qld.gov.au>]

I am interested to know how best to go about getting the rows affected
count from a statement such as this using iBatis:
 
    <statement id="Measure-Snapshot-Insert"
parameterClass="System.Int32">
      INSERT INTO [Measure]
      (LocationId,ReportGroupId,MeasureTypeId,PeriodId,SnapshotId,Value)
      SELECT LocationId,ReportGroupId,MeasureTypeId,PeriodId,$value$ AS
SnapshotId,
      TotalValue FROM [Measure] WHERE SnapShotId IS NULL
    </statement>    
 
I first tried this as an <Update> statement, but received error about
not using INSERT into an UPDATE so I revered to a general <statement>.
 
Do only Update and Delete return rowcounts?   
Should I append a SELECT @@ROWCOUNT to the end of the statement?
I'd rather not wrapper this in a stored proc if possible.
 
Thanks,
Jason


This correspondence is for the named persons only. 
It may contain confidential or privileged information or both. 
No confidentiality or privilege is waived or lost by any mis transmission. 
If you receive this correspondence in error please delete it from your system immediately and notify the sender. 
You must not disclose, copy or relay on any part of this correspondence, if you are not the intended recipient. 
Any opinions expressed in this message are those of the individual sender except where the sender expressly, 
and with the authority, states them to be the opinions of the Department of Emergency Services, Queensland.

Re: Rows affected count from bulk insert select.

[Gilles Bayon <ib...@gmail.com>]

You can do it, see inspiration below

<statement id="InsertCategory" parameterClass="Category" resultClass="int">
insert into Categories (Category_Name, Category_Guid) values (#Name#,
#Guid:UniqueIdentifier#);
select SCOPE_IDENTITY() as value
</statement>

int key = (int)sqlMap.Insert("InsertCategory", category);
Replace
select SCOPE_IDENTITY() as value
by
SELECT @@ROWCOUNT as value



On 1/15/07, Jason Finch <jf...@emergency.qld.gov.au> wrote:
>
>  I am interested to know how best to go about getting the rows affected
> count from a statement such as this using iBatis:
>
>     <statement id="Measure-Snapshot-Insert" parameterClass="System.Int32">
>       INSERT INTO [Measure]
>       (LocationId,ReportGroupId,MeasureTypeId,PeriodId,SnapshotId,Value)
>       SELECT LocationId,ReportGroupId,MeasureTypeId,PeriodId,$value$ AS
> SnapshotId,
>       TotalValue FROM [Measure] WHERE SnapShotId IS NULL
>     </statement>
>
> I first tried this as an <Update> statement, but received error about not
> using INSERT into an UPDATE so I revered to a general <statement>.
>
> Do only Update and Delete return rowcounts?
> Should I append a SELECT @@ROWCOUNT to the end of the statement?
> I'd rather not wrapper this in a stored proc if possible.
>
> Thanks,
> Jason
>   This correspondence is for the named persons only.
> It may contain confidential or privileged information or both.
> No confidentiality or privilege is waived or lost by any mis transmission.
>
> If you receive this correspondence in error please delete it from your
> system immediately and notify the sender.
> You must not disclose, copy or relay on any part of this correspondence,
> if you are not the intended recipient.
> Any opinions expressed in this message are those of the individual sender
> except where the sender expressly,
> and with the authority, states them to be the opinions of the Department
> of Emergency Services, Queensland.
>



-- 
Cheers,
Gilles

<a href="http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>