How to check if something has been inserted using INSERT SELECT (T-SQL)

Question

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 this insert has inserted anything at all. Ideally, I would like to set the variable @insertedCountto the number of rows inserted, even if it is 0.

I am currently using:

set @insertedCount = @@ROWCOUNT

But it only seems that the last number of rows inserted is the problem that if the operator INSERT SELECTdid not insert anything, it @@ROWCOUNTdoes not return 0.

Answer 1

You can try using OUTPUTone that returns one row in the inserted row; sort of:

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

firstId , .
- output into table-var, select count(*) from @tableVar , .