June 5, 2008 at 11:55 am
Hi all
I have a cursor that select data from one table and then insert it into two tables: a master table and a child.
All records are inserted to the two tables fine. However, the first record is skipped and the last record is added twice. Not sure why.
I tried to add the first record separate and it works fine. I also delete the first record from the source table, but then skips the second record.
So I know is nothing wrong with the data.
Any idea? Thanks in advance
Here is the code for the cursor:
declare @ModelNumber varchar(50),
@VendorID int,
@AccountNumber varchar(50),
@NewAccountNumber varchar(50),
@LocationCity varchar(50),
@LocationState varchar(50),
@POCUserName varchar(50),
@AssigneeUserName varchar(50),
@ProgramCode varchar(50),
@ProgramName varchar(50),
@Comments varchar(200),
@ActivationDate datetime,
@PIN varchar(50),
@SimChipNumber varchar(50),
@CellPhone varchar(50),
@TetheredDate datetime
declare c4 cursor for
select MODEL,VendorID,[ACCOUNT NUMBER],[NEW ACCOUNT],LOCATION,STATE,POCUserName,UserName,[PROGRAM CODE],
PROGRAM,COMMENTS,[ACTIVATION DATE],PIN,[SIM CHIP NUMBER],[CELL PHONE],
[TETHERED DATA]
from [BLACKBERRY]
open c4
fetch next from c4
into @ModelNumber,@VendorID,@AccountNumber,@NewAccountNumber,@LocationCity,@LocationState,@POCUserName,@AssigneeUserName,
@ProgramCode,@ProgramName,@Comments,@ActivationDate,@PIN,@SimChipNumber,@CellPhone,@TetheredDate
while @@fetch_status = 0
begin
fetch next from c4
into @ModelNumber,@VendorID,@AccountNumber,@NewAccountNumber,@LocationCity,@LocationState,@POCUserName,@AssigneeUserName,
@ProgramCode,@ProgramName,@Comments,@ActivationDate,@PIN,@SimChipNumber,@CellPhone,@TetheredDate
insert into tblAsset(AssetTypeID,ModelNumber,VendorID,AccountNumber,NewAccountNumber,LocationCity,LocationState,POCUserName,
AssigneeUserName,ProgramCode,ProgramName,Comments,ActivationDate)
values(6,@ModelNumber,@VendorID,@AccountNumber,@NewAccountNumber,@LocationCity,@LocationState,@POCUserName,@AssigneeUserName,
@ProgramCode,@ProgramName,@Comments,@ActivationDate)
insert into tblClassBlackberry(AssetID,PIN,SimChipNumber,CellPhone,TetheredDate)
values(SCOPE_IDENTITY(),@PIN,@SimChipNumber,@CellPhone,@TetheredDate)
end
close c4
deallocate c4
June 5, 2008 at 12:27 pm
Your sequence of events in your code is wrong. Let's recap:
-you create cursor and variables.
- you fetch data.
- you enter the while loop, and not having done anything with the data you had in step1, you fetch again.
- you then do inserts, and loop.
Solution: move the second FETCH to the last step within the loop.
Of course - the biggest mystery to me is why the cursor at all? Look up the OUTPUT clause, and save yourself a LOT of headaches. With a little more info/some sample data, I don't think it would be all that hard to drop that cursor altogether. There is bound to be more than one natural key in that data - the question is which to use.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 5, 2008 at 2:13 pm
Oops...Thank you very much! that did the trick!
Why cursor? cursors seem to do what i need...
Thanks again for the prompt response
June 6, 2008 at 9:38 am
I understand that - it's just terribly inefficient for what you're trying to do. I suspect that the appropriate SET-based solution (using the OUTPUT clause as previously mentioned) would likely do this several hundred times faster, and be less taxing on the system, than the cursor.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 7, 2008 at 8:29 pm
natividad.castro (6/5/2008)
Why cursor? cursors seem to do what i need...
Heh.
And, I suppose that walking will get you where you need to go.
However, not as fast or as easily as you may want.
Especially after you've learned how to drive.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply