Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 number of rows inserted

Author  Topic 

wided
Posting Yak Master

218 Posts

Posted - 2013-02-20 : 09:02:34
Following a request insert, I would like to retrieve the number of rows inserted

thank you

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 09:09:48
INSERT Statement
@@ROWCOUNT will give number of rows affected by recent DML

--
Chandu
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 09:10:52
You can use the @@ROWCOUNT system function. Couple of things to keep in mind about using it are:

a) If you need to use it later, capture it immediately after the insert statement and store it in a variable.
DECLARE @rows_inserted INT;
-- your insert statement here
SET @rows_inserted = @@ROWCOUNT;


b) If you are using MERGE, @@ROWCOUNT would reflect the number of rows affected (including updates, deletes0 rather than just the number of rows inserted.
Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2013-02-20 : 09:23:11
MY QUERY IS THIS:
INSERT INTO U41_TachesWorkFlowDetail
select t1.WorkFlowID,t3.EmployeID , @wEtape
from Inserted t1, h11_employe t2 , U41_UserValidateWF t3
where t1.employeId= t2.EmployeID
and t2.ChefHierarchiqueId= t3.EmployeID
and profil = @wEtape


PRINT @@ROWCOUNT

is returns 0 (why), at least one line is inserted

return 0
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 09:41:01
Assuming you do not have any other statements between the insert and the print, it should print out the number of rows inserted. If it is printing out 0, no rows were inserted. How were you verifying that at least one row was inserted?
Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2013-02-20 : 09:57:11
I checked the table directly in the database
Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2013-02-20 : 10:18:59

it insert this in my table(U41_TachesWorkFlowDetail)
26 28 35035 CH
27 28 35035 DRH

I need to know if inserted a row to stop processing

just
26 28 35035 CH

after insert, i do this:

if @@ROWCOUNT > 0 break

I would like to exit the loop when inserting a row in this table

Are there any other means
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 11:12:14
That should work. How is your logic set up? does your while loop have a begin and end? See this example - it should run through the loop only once.
WHILE (1=1)
BEGIN
SELECT '1';
IF (@@ROWCOUNT > 0) BREAK;
END
Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2013-02-21 : 04:16:16
Hi James



WHILE @Status=0
BEGIN
FETCH next FROM curs INTO @num, @wEtape
SELECT @Status = @@fetch_status
IF @Status <> 0 BREAK --status curs
IF @flag <> 0 BREAK --
if @wEtape is not null AND @flag =0
begin
INSERT INTO U41_TachesWorkFlowDetail
select t1.WorkFlowID,t3.EmployeID , @wEtape
from Inserted t1, h11_employe t2 , U41_UserValidateWF t3
where t1.employeId= t2.EmployeID
and t2.ChefHierarchiqueId= t3.EmployeID
and profil = @wEtape
end
if @@rowcount >0
select @flag = 1

end --while


This is my query

I added one which is flag = 1 if @@ rowcount> 0 (thus recording added)

Then I added:

IF @ flag <> 0 BREAK

I put "print" in my procedure

the result:

wEtape = CH
flag =1
wEtape = DRH
flag = 1


normally the procedure should stop the insersion the "CH"
but the system inserts CH and DRH
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-21 : 04:30:02
Where are you assigning flag value at first time?

--
Chandu
Go to Top of Page
   

- Advertisement -