Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    36

    Unanswered: select statement

    i'm new to sqlserver. is there a way to step throught each row returned in select statement. what i have is a number of rows inserted by a bulk insert into a staging table. from there i need to insert each row x # of times into another table depending on the 'quantity' column in the staging table.


    thanks,
    e3witt

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    we do have a while loop in sql server ....

    http://msdn.microsoft.com/library/de...wa-wz_6oyt.asp
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    May 2004
    Posts
    36
    thanks, but i suppose i didn't explain properly. either way i figured out my how to do it.

    thanks,

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You almost never need to "step through" a table in SQL Server, and it is rarely an efficient method of processing. In this case you can solve your problem by
    creating a table of sequential numbers 0 through MAX REQUIRED ROWS.

    Then structure an INSERT query like this:

    Insert into PRODUCTIONTABLE
    select YOURCOLUMNS
    from STAGINGTABLE,
    SEQUENTIALNUMBERTABLE
    where SEQUENTIALNUMBERTABLE.VALUE < STAGINGTABLE.QUANTITYCOLUMN
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2003
    Posts
    706
    You may also find it most efficient to do the overall job in smaller stages, committing a transaction each time.

    For example, let's say that your bulk-input consists of 1,000 rows. If you perform the insert operation (by whatever means) as "10 groups of 100 rows each" or even "100 groups of 10," the total amount of "undo-load" on the system remains much smaller. The total amount of resources demanded by each query while in-progress is much smaller, and so, its performance impact on the system is much smaller.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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