Begin End Block inserting too many records

Question

Begin End Block inserting too many records

dorisdelaney

SSC Rookie

Points: 47
More actions
July 28, 2014 at 6:51 am

#148498

I have a temporary table that can have anywhere from one to maybe 15 records each day. I am trying to insert into another table using SCOPE_IDENTITY. Sometimes it works fine and other days it inserts thousands of records. I am using a BEGIN END block to accomplish this task. Does anyone have ideas on what I am doing wrong?
set @i = 1
set @numrows = (select count(*) from @temp)
if @numrows > 0 while (@i <= (select max(idx) from @temp)
BEGIN
Insert into identitytable values(1)
set @newid = SCOPE_IDENTITY()
insert into secondtable (ID, otherstuff, otherstuff1)
select @newid, otherstuff, otherstuff1 from @temp where @i = idx
set @i = @i +1
END

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

dorisdelaney (7/28/2014)
I have a temporary table that can have anywhere from one to maybe 15 records each day. I am trying to insert into another table using SCOPE_IDENTITY. Sometimes it works fine and other days it inserts thousands of records. I am using a BEGIN END block to accomplish this task. Does anyone have ideas on what I am doing wrong?
set @i = 1
set @numrows = (select count(*) from @temp)
if @numrows > 0 while (@i <= (select max(idx) from @temp)
BEGIN
Insert into identitytable values(1)
set @newid = SCOPE_IDENTITY()
insert into secondtable (ID, otherstuff, otherstuff1)
select @newid, otherstuff, otherstuff1 from @temp where @i = idx
set @i = @i +1
END

From what you have posted, not sure if I see a problem per say. Obviously there is more to what is going on than this small piece and with it taken out of context hard to say what may be wrong.

One thing I will say is that I don't think you need to be doing this in a loop. With more information I am sure we could put together a set based routine that could replace your current solution.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 2

Things are going wrong with this line, clearly:

select @newid, otherstuff, otherstuff1 from @temp where @i = idx

Does the actual version of the code select its data from multiple tables, by any chance?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

dorisdelaney SSC Rookie Points: 47 More actions · Answer 3

No, all of the data comes from the one temporary table. I have the SCOPE_IDENTITY in there because it is constantly being used and I didn't want to put the identities in the temporary table in case those numbers were being pulled by others at the same time.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 4

Here is how I would try to do it.

Do an INSERT to your identities table such that it generates all of the idents you need in one hit.

Then use an OUTPUT clause to fire the newly created identities into a temp table or table variable.

Then do your insert to secondtable in one shot.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Phil Parkin (7/28/2014)
Here is how I would try to do it.
Do an INSERT to your identities table such that it generates all of the idents you need in one hit.
Then use an OUTPUT clause to fire the newly created identities into a temp table or table variable.
Then do your insert to secondtable in one shot.

Agreed. A good way to go.

dorisdelaney SSC Rookie Points: 47 More actions · Answer 6

Thanks for the suggestion. I will give that a shot. I never really like the idea of a loop but never thought of the OUTPUT option so thank you.