views:

38

answers:

1

I have an insert statement:

insert into parentTbl
select firstId, secondId, thirdId, dateTm
from importTbl
where codeId = @codeIdParam

I need to reliably find out if that insert inserted anything at all. Ideally, I would like to set a @insertedCount variable to the number of rows inserted, even if that is 0.

I am currently using:

set @insertedCount = @@ROWCOUNT

But that only seems to get the last number of inserted rows - the problem is that if the INSERT SELECT statement did not insert anything the @@ROWCOUNT does not return 0.

+2  A: 

You could try using an OUTPUT clause, which would return one row per inserted row; something like:

insert into parentTbl
output inserted.firstId
select firstId, secondId, thirdId, dateTm
from importTbl
where codeId = @codeIdParam

This would give you a resultset with the firstIds of every inserted row, which you could then run a count on.
One way would be to output into a table-var, and then do a select count(*) from @tableVar at the end to get your insert-count.

tzaman
Ultimately, I've taken the SELECT of the INSERT SELECT statement and set it to return COUNT(*) to a variable - though, of course, this happens before the INSERT SELECT, which is now wrapped in an IF block.
Matt W