Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    1

    Unanswered: Problem with INSERT statement inside a loop

    I'm trying to "copy" some rows in my table, making a variable number of copies.

    For some reason, my loop works fine if don't have the insert statement in it (I tested with a select statement instead)

    With the insert statement, it will loop continuously inserting rows.

    Any idea what the difference is when I am using an insert statement?

    Here's a snippet of my code, where the while loop is:

    /* loop for each copy above one */
    while @copy > 1
    begin
    INSERT INTO bib (bib#, tag, tagord, text)
    VALUES (@bibno, '949', @newtagord, @text)
    select @newtagord = @newtagord + 50
    select @copy = @copy - 1
    end

  2. #2
    Join Date
    Feb 2009
    Posts
    1
    Thanks for the answer

  3. #3
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Have you initialised @copy before starting the loop? At what value does @copy starts? I'm not sure if this is the problem, but it might be.
    I'm not crazy, I'm an aeroplane!

  4. #4
    Join Date
    Dec 2008
    Posts
    1
    you can better do this using a hash table and then update the records you want to modify.

    this is waaaaaaaaaaaaay slow.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    example of using a numbers table
    Code:
    INSERT INTO bib (bib#, tag, tagord, text) 
    select @bibno, '949', number*50, @text
    from master..spt_values 
    where type='P'
      and number between 1 and @copy

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by pdreyer
    example of using a numbers table
    Code:
    INSERT INTO bib (bib#, tag, tagord, text) 
    select @bibno, '949', number*50, @text
    from master..spt_values 
    where type='P'
      and number between 1 and @copy
    Can you educate us all on how spt_values works or should be used?

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Can be used for numbers up to 1024 else create your own numbers table

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    Numbers table

    Here is a link on how to create a numbers table
    It is not ASE specific but works similar
    adam machanic now blogs at http://sqlblog.com : You REQUIRE a Numbers table!
    And also some uses of a numbers table
    Why should I consider using an auxiliary numbers table

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    many thanks for the extra info
    mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •