Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    14

    Unanswered: Order of Rows Imported Into SQL Server 2000 From Excel Not Correct

    Hello,

    I am new to SQL Server 2000 and I am trying out the 'Import' feature. I successfully imported an Excel spreadsheet of 8144 rows into a pre-defined (empty) SQL Server 2000 table but for some reason the order is not entirely right. In the original spreadsheet the order is by Stock Ticker in column A and then by Date in column B, however, when I check the table in SS2000 it is not sequenced correctly.

    Can someone explain what happened?

    Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    Hi

    Data is not stored "in order" (one of the rules of relational databases)...to see it ordered as you did in excel
    specify the columns it must order by

    Des

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by DesmondX
    Hi

    Data is not stored "in order" (one of the rules of relational databases)...to see it ordered as you did in excel
    specify the columns it must order by

    Des
    Unless there is clustered index for table...

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by snail
    Unless there is clustered index for table...
    And that's just for the initial load....subsequent inserts may be thrown around to what ever available free pages there are...

  5. #5
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    14
    Thanks to all who replied!

    I guess I was kind of thinking ahead, since my ultimate intention is to use the table in a sorted order and 'step thru' the rows one-by-one in sequential order. This is essential for the task I wish to accomplish. The only thing I can think of to get this done would be to build cursors, but a book I am reading on SQL Server 2000 strongly suggests not using cursors unless absolutely necessary.

    Can anyone enlighten me on how I can possibly 'step thru' a sequentially ordered set without using cursors?

    Many thanks to all in advance!

    Joe

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by aussie
    Thanks to all who replied!

    I guess I was kind of thinking ahead, since my ultimate intention is to use the table in a sorted order and 'step thru' the rows one-by-one in sequential order. This is essential for the task I wish to accomplish. The only thing I can think of to get this done would be to build cursors, but a book I am reading on SQL Server 2000 strongly suggests not using cursors unless absolutely necessary.

    Can anyone enlighten me on how I can possibly 'step thru' a sequentially ordered set without using cursors?

    Many thanks to all in advance!

    Joe
    Hey EVEN with a cursor you'd need an Order by...

    What do you want to do with your data...

    Any sample data (IN INSERT Form) DDL and/or DML will help us

  7. #7
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    14
    I don't disagree that I would need an 'Order by'; I simply don't know how I would 'step thru' the rows in the table and its subsets without the use of cursors.

    Here is a partial image of the data in my table:

    MRVC,11/14/2002,1.16,1.36,1.15,1.34,531100
    MRVC,11/15/2002,1.35,1.46,1.26,1.44,719800
    MRVC,11/18/2002,1.43,1.51,1.36,1.36,536400
    MRVC,11/19/2002,1.4,1.45,1.27,1.29,161000
    MRVC,11/20/2002,1.26,1.43,1.26,1.32,507400
    .......................
    .......................
    .......................
    ARTX,2/6/2003,0.53,0.54,0.47,0.51,177300
    ARTX,2/7/2003,0.51,0.52,0.47,0.51,68700
    ARTX,2/10/2003,0.5,0.56,0.45,0.52,133100
    ARTX,2/11/2003,0.52,0.52,0.47,0.49,88600
    ARTX,2/12/2003,0.5,0.52,0.49,0.51,138500
    .......................
    .......................
    .......................
    SCON,11/14/2002,1.06,1.16,1.06,1.1,48300
    SCON,11/15/2002,1.07,1.13,1.05,1.1,82400
    SCON,11/18/2002,1.15,1.15,1.02,1.05,228400
    SCON,11/19/2002,1.07,1.08,1.04,1.04,95900
    SCON,11/20/2002,1.05,1.07,1.02,1.06,98800


    The table is made up of 36 subsets of data based on stock symbol (e.g., MRVC, ARTX, SCON, etc.). I have a special 'driver' file which I use to decide which subset to SELECT next for processing. I then need to step thru the subset sequentially and process each row by date, oldest-to-newest.

    The author of the book I am reading says I should be able to build a set-based solution to almost every task without using cursors, but I don't see how I would 'step thru' the rows without cursors.

    Any ideas?

    Thanks again.

    Joe

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I agree with that author...

    What's the name of the table (and columns..it help when I try and build some code..DDL would really help)

    Since you want to process in "order" what column would you order on...

    (I got oldest to newest..but what else...I'm assuming the date represents oldest to newest)

    What do you want to apply to the data? How do you want to manipulate it?

    Also for example for MRVC (I'm guessing that's a group), what would the end result look like?

    I Think we're getting closer...

    gotta run though..take a look in the am
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

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

  9. #9
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    to avoid cursors you can always throw them in a temp table with an auto-id column, then use a while loop to run through them - similar to cursor but I find it less resource intensive.

    if you do use cursors, use the fast_forward option...

    have a good think though if there is any logical set by which each group of updates can be defined, as set based is the best...

    good luck

  10. #10
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    14
    DesmondX and Brett Kaiser,

    Thank you both for your suggestions. Referring back to the sample data that I listed previously, the entire table represents stock history for about 36 specific stocks (tickers). Each row in my table has the following columns: TICKER, DATE, OPEN, HIGH, LOW, CLOSE, VOLUME.

    As I mentioned, I have a 'driver' file --- which I 'step thru' (or 'read' sequentially --- that I use to determine which subset of the table should be processed next. In fact, the driver file will also dictate at what point in the subset I need to begin processing. So, for example, the driver may say that ticker SCON is to be processed, beginning from 05/23/2003 onwards. Let's say that the range of dates in my SCON subset is from 11/14/2002 to say, 10/15/2003. In that case I will immediately do a SELECT of all available SCON rows dated 05/23/2003 and later, and ORDER them BY the DATE. I then initiate a 'pseudo'-stock trade and 'step thru' the SCON rows in chrono order, examining them and doing calcs to determine profit/loss and whether to close the trade.

    This task of SELECTing TICKER subsets and then processing each one continues until all of the driver rows have been exhausted ('read').

    The idea of a 'temp' table is intriguing. I have never tried it. I suppose I can get info in Books Online.

    I continue to remain open to any and all future ideas, and I thank you all for replying.

    Joe

  11. #11
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    one more tip - if you go the temp table root, use a memory table (@tablename) as opposed to a tempdb table (#tablename) - they are quicker as are stored in memory, not on disk

    Books online should have all you need...

    Des

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    to avoid cursors you can always throw them in a temp table with an auto-id column, then use a while loop to run through them - similar to cursor but I find it less resource intensive.
    hmmmmmm... a while loop with a temp table...with no cursor...how exactly do you do this?

    if you do use cursors, use the fast_forward option...
    No....no cursors..

    You've got a driver...that's ok...that just makes it a join..I see no pronlem there..

    I need...and very specifically....what you plan to do with the rows...

    do you have different rules for a bunch of scenarios per row?

    If it's Tuseday evening at 6:00 and the row is purple...do this

    If it's Wednesday...yada yada yada...

    Or do you have 1 rule (which is what it sounds like)

    In either case 1 or many rules...it probably can be done in 1 statement..

    Can you tell me what the rule is that needs to be applied?

    Do you know what DDL is? If so post the DDL for the Driver and the table you want to go against..

    We've got the sample data from the base...a sample of the driver would help...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

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

Posting Permissions

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