December 6, 2009 at 10:08 pm
I'm trying to populate my db.
Here first the cursor populates with say 10 rows and I want for each row I get I should have 1500 row in someother table.
My query here catches only first row from cursor and populates 1500 row in other table.. And it does not take the 2 data from cursor What is that I'm missing here
(This is only one time activity.. So cursor is ok)
declare @resnr int ,@res_seqnr int, @tot int set @resnr=1144894 set @tot = @resnr + 1500 print @tot
declare results_cur cursor for
select sequencenr from sequenctbl where Inst = 8323
open results_cur
fetch next from results_cur into @res_seqnr
while @@fetch_status = 0
BEGIN
While (@resnr <= @tot)
begin
INSERT INTO [my_tbl]
([SequenceNr],[TimeInfo],[Conc],[Excluded],[ResultNr]
,[SampleRunNr])
VALUES (@res_seqnr, getdate(),100.000000,0,@resnr,@resnr)
print 'in inner while'
set @resnr= @resnr +1
end
fetch next from results_cur into @res_seqnr end
CLOSE results_cur
DEALLOCATE results_cur
December 7, 2009 at 12:12 am
Where is the other END and before that you are closing and deallocating cursor?
while @@fetch_status = 0
BEGIN
---------------------------------------------------------------------------------
December 7, 2009 at 12:24 am
missed in pasting query
END is right before closing the cursor:
WHILE...
...
...
end
CLOSE instrument
DEALLOCATE instrument
December 7, 2009 at 12:36 am
Can you paste the complete query out here please? (if you can)
---------------------------------------------------------------------------------
December 7, 2009 at 12:56 am
declare @resnr int ,@res_seqnr int, @tot int
set @resnr=1144894
set @tot = @resnr + 1500
print @tot
declare results_cur cursor for
select sequencenr from sequenctbl where Inst = 8323
open results_cur
fetch next from results_cur into @res_seqnr
while @@fetch_status = 0
BEGIN
While (@resnr <= @tot)
begin
INSERT INTO [my_tbl]
([SequenceNr],[TimeInfo],[Conc],[Excluded],[ResultNr]
,[SampleRunNr)
VALUES (@res_seqnr, getdate(),100.000000,0,@resnr,@resnr)
print 'in inner while'
set @resnr= @resnr +1
end
fetch next from results_cur into @res_seqnr end
END
CLOSE results_cur
DEALLOCATE results_cur
December 7, 2009 at 1:09 am
1. Not sure if it works as there are syntactical errors.
2. You are using one variable for cursor value and another for the 'inner while loop'
3. In the first loop of the cursor itself, the value of @resnr will be equal to @tot and hence there after its not going inside the 'inner while' during cursors second and thereafter loop! You might need to think of resetting the value of @resnr once it comes out of the inner while loop!
While (@resnr <= @tot)
begin
INSERT INTO [my_tbl]
([SequenceNr],[TimeInfo],[Conc],[Excluded],[ResultNr]
,[SampleRunNr)
VALUES (@res_seqnr, getdate(),100.000000,0,@resnr,@resnr)
print 'in inner while'
set @resnr= @resnr +1
end
---------------------------------------------------------------------------------
December 7, 2009 at 1:48 pm
khushbu (12/6/2009)
(This is only one time activity.. So cursor is ok)
No it isn't. Professionals do not turn in inferior work just because it is for one-time use.
Here is how to do it without any Loops or Cursors. It is also about 100x faster, and easier to read to boot:
declare @resnr int
set @resnr=(1144894-1)
INSERT INTO [my_tbl]
([SequenceNr],[TimeInfo],[Conc],[Excluded],[ResultNr]
,[SampleRunNr])
SELECT
sequencenr, getdate(),100.000000,0,@resnr+Num,@resnr+Num
FROM sequenctbl
CROSS JOIN
(
SELECT TOP 1500 ROW_NUMBER() OVER(ORDER BY id) AS Num
FROM master..syscolumns AS Numbers
WHERE Num <= 1500
) AS Tally
WHERE Inst = 8323
If you already have your own Tally or Numbers table, you can swap that in for the subquery instead.
[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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply