Looping within a loop, without wanting it to...
-
The code below is for some reason that is probably obvious to others, but I can't see through the forst of having been fighting with, keeps looping within a loop it seems.
I have for the current test sample 153 records to insert.
This code is inserting the 153 records 153 times (153x153). 🙁
I've remmed, and moved many of the pieces, but that just completely breaks it.
I'm hoping someone who's less blearied eyed than I currently am, can see where I'd made the loop within a loop, so that just the 153 records insert.
Yes, it's using cursors, and I've read the many articles for and against them. The imports for this particular piece (the only piece in thousands of lines of code using cursors) will only average about 50 to 250 records at a time as they are imported from a Lotus Approach dbase iv file.
This is the final piece to finish this. All the other updtaes, inserts, etc. work great, except this last piece which needs to assign a new "RECNUM" as a unique number but NOT the identity number. This is because RECNUM is legacy data, that for a while will still have to be exported back out to the Approach dbf files until they finish with a front end (I've been doing all the backend sp's). I have an ALLID for a non-human-created identity (And primary key). Unfortunately, this means I couldn't make recnum identity(1,1), so I had to come up with some code to get the last highest recnum, and increment it by 1, and then insert accordingly to assign those recnums, meanwhile letting allid be assigned for the pk and such.
So, if someone could please point out how to get the looping within a loop to stop, I'd be very grateful) and finally done with this key piece of the project.
Thanks...
-- TRY 4b, with cursors
-- Almost works, except that it inserts the 153 rows, many times, adds up to 23,409 rows.
-- Which is 153 x 153. So it's looping within the loop...
--===============================================================================
-- Insert into AllTmp4 the remaining records in Project01Tmp3.
-- These do not have RECNUMs assigned and need them assigned, so the autoincrement feature here is added to give each record a recnum
-- and then insert it into ALL as a new record.
---
/* insert into AllTmp4 from Project01Tmp3 those
records that are left after update checks/updates/deletes completed, now insert those
remaining records that do not have a recnum, and weren't found to match by fname/lname/market/phone/etc as an update to existing recnums either. Just as new inserts */
CREATE PROCEDURE dbo.usp_InsertInfoAllFromProject01Tmp3NewRecordsAndGiveNewRecnums
AS
declare @newRecnum bigint
declare @highRecnum bigint
declare @id int
set @highRecnum = (select MAX(RECNUM) from [ALL])
set @newRecnum=@highRecnum+1
--BEGIN
--declare @id int
--WHILE @@FETCH_STATUS=0
BEGIN TRANSACTION
DECLARE CursorQuery CURSOR FOR
SELECT Project01ID from [Project01Tmp3]
OPEN CursorQuery
FETCH NEXT FROM CursorQuery
INTO @id
PRINT 'Record Status'+ CAST(@@FETCH_STATUS AS varchar)
WHILE @@FETCH_STATUS=0
BEGIN
--WHILE @@FETCH_STATUS=0
INSERT INTO [ALLtmp4](FIRSTNAME, LASTNAME, HOMEPHONE, WORKPHONE,
EXT, FAX, CELL, EMAIL, TITLE, DEPT, FIRM,
ADDRESS, CITY, STATE, ZIP, COUNTRY, INDUSTRY, YRLY_REV, EMPLOYEES, URL, FORTUNE_1K,
COMMENTS, PROJNUM, CLIENT_ID, [DATE], PASTPART, RESULT, RECRUITER, RECRUIT_SOURCE,
CALL_RESULT, MOD_DATE, DB, GENDER, YOB, KIDS, MARKET, MARITAL, CHILD1_DOB, CHILD1_GENDER,
CHILD2_DOB, CHILD2_GENDER, CHILD3_DOB, CHILD3_GENDER, HH_INCOME, MAIL_ADDRESS, MAIL_CITY,
MAIL_STATE, MAIL_COUNTRY, MAIL_ZIP, HOME_EMAIL, ETHNICITY, RECNUM)
SELECT FIRSTNAME, LASTNAME, HOMEPHONE, WORKPHONE, EXT, FAX, CELL, EMAIL, TITLE, DEPT, FIRM,
ADDRESS, CITY, STATE, ZIP, COUNTRY, INDUSTRY, YRLY_REV, EMPLOYEES, URL, FORTUNE_1K,
COMMENTS, PROJNUM, CLIENT_ID, [DATE], PASTPART, RESULT, RECRUITER, RECRUIT_SOURCE,
CALL_RESULT, MOD_DATE, DB, GENDER, YOB, KIDS, MARKET, MARITAL, CHILD1_DOB, CHILD1_GENDER,
CHILD2_DOB, CHILD2_GENDER, CHILD3_DOB, CHILD3_GENDER, HH_INCOME, MAIL_ADDRESS, MAIL_CITY,
MAIL_STATE, MAIL_COUNTRY, MAIL_ZIP, HOME_EMAIL, ETHNICITY,
(@newRecnum)
FROM Project01Tmp3
set @newRecnum=@newRecnum+1
FETCH NEXT FROM CursorQuery
INTO @id
END
CLOSE CursorQuery
DEALLOCATE CursorQuery
RETURN
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('There was an error', 16, 1)
RETURN
END
COMMIT TRANSACTION
GO
-
(@newRecnum)
FROM Project01Tmp3
WHERE <A href="mailto
roject01ID=@id">Project01ID=@id /*new*/*thinking of a cursor less solution
DECLARE @highRecnum bigint
set @highRecnum = (select ISNULL(MAX(RECNUM),0)+1 from [ALL])
INSERT INTO [ALLtmp4](FIRSTNAME, LASTNAME, HOMEPHONE, WORKPHONE,
EXT, FAX, CELL, EMAIL, TITLE, DEPT, FIRM,
ADDRESS, CITY, STATE, ZIP, COUNTRY, INDUSTRY, YRLY_REV, EMPLOYEES, URL, FORTUNE_1K,
COMMENTS, PROJNUM, CLIENT_ID, [DATE], PASTPART, RESULT, RECRUITER, RECRUIT_SOURCE,
CALL_RESULT, MOD_DATE, DB, GENDER, YOB, KIDS, MARKET, MARITAL, CHILD1_DOB, CHILD1_GENDER,
CHILD2_DOB, CHILD2_GENDER, CHILD3_DOB, CHILD3_GENDER, HH_INCOME, MAIL_ADDRESS, MAIL_CITY,
MAIL_STATE, MAIL_COUNTRY, MAIL_ZIP, HOME_EMAIL, ETHNICITY, RECNUM)
select
FIRSTNAME, LASTNAME, HOMEPHONE, WORKPHONE, EXT, FAX, CELL, EMAIL, TITLE, DEPT, FIRM,
ADDRESS, CITY, STATE, ZIP, COUNTRY, INDUSTRY, YRLY_REV, EMPLOYEES, URL, FORTUNE_1K,
COMMENTS, PROJNUM, CLIENT_ID, [DATE], PASTPART, RESULT, RECRUITER, RECRUIT_SOURCE,
CALL_RESULT, MOD_DATE, DB, GENDER, YOB, KIDS, MARKET, MARITAL, CHILD1_DOB, CHILD1_GENDER,
CHILD2_DOB, CHILD2_GENDER, CHILD3_DOB, CHILD3_GENDER, HH_INCOME, MAIL_ADDRESS, MAIL_CITY,
MAIL_STATE, MAIL_COUNTRY, MAIL_ZIP, HOME_EMAIL, ETHNICITY,
,Ranktable.ranknumber+@highRecnum AS newRecnum
from Project01Tmp3
inner join
(
select project1.projectid ,COUNT_BIG(*) AS ranknumber
from Project01Tmp3 project1
inner join Project01Tmp3 project2
on project1.Project01ID >=project2.Project01ID
group by project1.Project01ID
) Ranktable on projects.Project01ID =Ranktable.Project01ID
should do it
-
Thanks, that helped. I also had to edit the fact that it was iterating the @id twice (so was only insertint 77 records when I added your fix for the cursor based option).
Thanks very much!
I'm going to take a few minutes to see if I can modify the non-cursor based solution you suggested to work. Since I'd much rather run it without cursors, but I also can't take forever to figure it out.
The "ranktable" part of your code I don't quite get yet, and it doesn't work "as is", but I'll stare at it for a bit, and see if I can get it working correctly.
If not, I'll post my attempts for a little more guidance.
Below is the code for the cursor based versions fully functioning correctly now, in case any one else comes aross similar issues.
Thanks again!
-- TRY 4h, with cursors
--===============================================================================
-- Insert into AllTmp4 the remaining records in Project01Tmp3.
-- These do not have RECNUMs, so the autoincrement feature is added to give each record a recnum
-- and then insert it into ALL as a new record.
---
/* insert into Project01ShortTempInsertsWithRecnumAlready1 from Project01Tmp3 those
records that are left after update checks/updates/deletes completed, now insert those
remaining records that don't have a recnum */
CREATE PROCEDURE dbo.usp_InsertInfoAllFromProject01Tmp3NewRecordsAndGiveNewRecnums
AS
-- This works as a cursor solution. Now try the cursor less solution, if can get working
-- quickly use the cursorless solution, otherwise resort to using this cursor solution.
-- select * from AllTmp4
-- delete from AllTmp4
-- drop table AllTmp4
-- exec usp_CreateAllTmp4table
declare @newRecnum bigint
declare @highRecnum bigint
declare @id int
set @highRecnum = (select MAX(RECNUM) from [ALL])
--set @newRecnum=@highRecnum+1
BEGIN TRANSACTION
DECLARE CursorQuery CURSOR FOR
SELECT Project01ID from [Project01Tmp3]
OPEN CursorQuery
FETCH NEXT FROM CursorQuery
INTO @id
WHILE @@FETCH_STATUS=0
BEGIN
-- FETCH NEXT FROM CursorQuery
-- INTO @id
INSERT INTO [ALLtmp4](FIRSTNAME, LASTNAME, HOMEPHONE, WORKPHONE,
EXT, FAX, CELL, EMAIL, TITLE, DEPT, FIRM,
ADDRESS, CITY, STATE, ZIP, COUNTRY, INDUSTRY, YRLY_REV, EMPLOYEES, URL, FORTUNE_1K,
COMMENTS, PROJNUM, CLIENT_ID, [DATE], PASTPART, RESULT, RECRUITER, RECRUIT_SOURCE,
CALL_RESULT, MOD_DATE, DB, GENDER, YOB, KIDS, MARKET, MARITAL, CHILD1_DOB, CHILD1_GENDER,
CHILD2_DOB, CHILD2_GENDER, CHILD3_DOB, CHILD3_GENDER, HH_INCOME, MAIL_ADDRESS, MAIL_CITY,
MAIL_STATE, MAIL_COUNTRY, MAIL_ZIP, HOME_EMAIL, ETHNICITY, RECNUM)
SELECT FIRSTNAME, LASTNAME, HOMEPHONE, WORKPHONE, EXT, FAX, CELL, EMAIL, TITLE, DEPT, FIRM,
ADDRESS, CITY, STATE, ZIP, COUNTRY, INDUSTRY, YRLY_REV, EMPLOYEES, URL, FORTUNE_1K,
COMMENTS, PROJNUM, CLIENT_ID, [DATE], PASTPART, RESULT, RECRUITER, RECRUIT_SOURCE,
CALL_RESULT, MOD_DATE, DB, GENDER, YOB, KIDS, MARKET, MARITAL, CHILD1_DOB, CHILD1_GENDER,
CHILD2_DOB, CHILD2_GENDER, CHILD3_DOB, CHILD3_GENDER, HH_INCOME, MAIL_ADDRESS, MAIL_CITY,
MAIL_STATE, MAIL_COUNTRY, MAIL_ZIP, HOME_EMAIL, ETHNICITY,
(@newRecnum)
FROM Project01Tmp3
WHERE <A href="mailto
roject01ID=@id">Project01ID=@id -- fix from sqlservercentral.com-- set @newRecnum=@newRecnum+1
FETCH NEXT FROM CursorQuery
INTO @id
END
CLOSE CursorQuery
DEALLOCATE CursorQuery
RETURN
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('There was an error', 16, 1)
RETURN
END
COMMIT TRANSACTION
GO
-
Woohoo!
Looks like I was able to fix a couple of minor typos and get it working. Thanks very much again for your help. You've no idea what a relief it is to finally have this particular monkey off my back! 😉
Here's the finished, working, non-cursor code:
-- TRY 9a, NO cursors, suggested by sqlservercentral.com
--===============================================================================
-- Insert into AllTmp4 the remaining records in Project01Tmp3.
-- These do not have RECNUMs, so the autoincrement feature is added to give each record a recnum
---
/* insert into Project01ShortTempInsertsWithRecnumAlready1 from Project01Tmp3 those
records that are left after update checks/updates/deletes completed, now insert those
remaining records that DO NOT have a recnum */
CREATE PROCEDURE dbo.usp_InsertInfoAllFromProject01Tmp3NewRecordsAndGiveNewRecnums
AS
DECLARE @highRecnum bigint
set @highRecnum = (select ISNULL(MAX(RECNUM),0)+1 from [ALL])
INSERT INTO [ALLtmp4](FIRSTNAME, LASTNAME, HOMEPHONE, WORKPHONE,
EXT, FAX, CELL, EMAIL, TITLE, DEPT, FIRM,
ADDRESS, CITY, STATE, ZIP, COUNTRY, INDUSTRY, YRLY_REV, EMPLOYEES, URL, FORTUNE_1K,
COMMENTS, PROJNUM, CLIENT_ID, [DATE], PASTPART, RESULT, RECRUITER, RECRUIT_SOURCE,
CALL_RESULT, MOD_DATE, DB, GENDER, YOB, KIDS, MARKET, MARITAL, CHILD1_DOB, CHILD1_GENDER,
CHILD2_DOB, CHILD2_GENDER, CHILD3_DOB, CHILD3_GENDER, HH_INCOME, MAIL_ADDRESS, MAIL_CITY,
MAIL_STATE, MAIL_COUNTRY, MAIL_ZIP, HOME_EMAIL, ETHNICITY, RECNUM)
select
FIRSTNAME, LASTNAME, HOMEPHONE, WORKPHONE, EXT, FAX, CELL, EMAIL, TITLE, DEPT, FIRM,
ADDRESS, CITY, STATE, ZIP, COUNTRY, INDUSTRY, YRLY_REV, EMPLOYEES, URL, FORTUNE_1K,
COMMENTS, PROJNUM, CLIENT_ID, [DATE], PASTPART, RESULT, RECRUITER, RECRUIT_SOURCE,
CALL_RESULT, MOD_DATE, DB, GENDER, YOB, KIDS, MARKET, MARITAL, CHILD1_DOB, CHILD1_GENDER,
CHILD2_DOB, CHILD2_GENDER, CHILD3_DOB, CHILD3_GENDER, HH_INCOME, MAIL_ADDRESS, MAIL_CITY,
MAIL_STATE, MAIL_COUNTRY, MAIL_ZIP, HOME_EMAIL, ETHNICITY, Ranktable.ranknumber+@highRecnum AS newRecnum
from Project01Tmp3
inner join
(
select project1.project01ID ,COUNT_BIG(*) AS ranknumber
from Project01Tmp3 project1
inner join Project01Tmp3 project2
on project1.Project01ID >=project2.Project01ID
group by project1.Project01ID
) Ranktable on project01Tmp3.Project01ID =Ranktable.Project01ID
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('There was an error', 16, 1)
RETURN
END
COMMIT TRANSACTION
GO
-
Glad you found it !
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply