Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2006
    Posts
    115

    Question Unanswered: Appened records from Table A to Table B via Insert?

    I tried to insert large pool of data from table A to table B. Table B is then exported to Excel for viewing purpose. However, i found that some of the rows are not inserted in order, they seemed to be inserted in between other rows that are inserted before them. May I know what is the problem? There is no key assigned to table B. Do we need to disable key in order to have items inserted in sequence order?

    in other word, instead of "insert", can we "append" records?
    Last edited by tingshen; 02-22-07 at 02:16.

  2. #2
    Join Date
    Feb 2007
    Posts
    62
    The only 'order' of your data is that specified in the ORDER BY clause of your query.
    There is no inherent order and don't let anyone tell you otherwise.

  3. #3
    Join Date
    Oct 2006
    Posts
    115
    ORDER BY is only restricted to the scope of the particular query. I am trying to look for a way to always insert records at the last row of a table.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by tingshen
    ORDER BY is only restricted to the scope of the particular query. I am trying to look for a way to always insert records at the last row of a table.
    Why?
    You need them ordered when your retrieve them using a query, not when they lie around on disk.

  5. #5
    Join Date
    Oct 2006
    Posts
    115
    why? because the destination table is meant for exporting purpose. If the order is not in the right sequence, the output will be in a mess.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no order to a relational database table -- you cannot always insert at the last row of a table, because there is no such thing as the last row of the table

    instead of exporting your table, why not export your query data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2006
    Posts
    115
    thanks r937 for the reminder! I almost forgot I'm on RDBMS! lol!

    Yah, exporting the data directly to excel should work! Will try out later!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Actually, he could probably get away with putting a clustered index on table B.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "get away with" in this case is a very good description of the situation

    i can "get away with" holding up a bank, as long as i don't mind the possibility that once in a while i'll get caught

    if he has a clustering index and exports the table, then every once in a while it won't be in the right order

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2006
    Posts
    115
    "clustered index"? the data has a lot of duplicates and only certain rows are unique, mostly are duplicates. That's why I have problem defining the right key to it. I ever thought of using timestamp to be the index key, however, it is still not unique enough

    let me make this clearer. It is actually a complicated rigid report. this is the structure of Table B:

    Emp#,EmpName,Day1,Day2,Day3,Day4,SpecialCode1,Spec ialCode2,SpecialCode3

    There are different branches where employees belongs to, and I did a while loop to handle the branches one by one.

    For each branch, it will actually fill up Table B with the following data:

    Report XYZ <-- first row (inserted into Emp# and EmpName, with 7 and 50 nvarchar respectively)
    Branch: <-- 2nd row (inserted into Emp# only)
    EmpNo Emp Name 1/1 1/2 1/3 1/4 123 456 789 <-- 3rd row, the header of the data, inserted into various fields
    1234567 John 8 4 6 2 1.05 2.05 3.05 <-- 4th row and subsequent rows under the same branch, indicating the detail data
    and the 5th row onwards are the same detail data, until all data in the branch is published. in fact, the detail data was only processed by 1 single query, only the headers are being "inserted manually" 1 by 1.

    I know it's a bit crazy to do it this way, unfortunately, my users are not ready for report servers, that's how I came out with this crazy idea and export them to excel for them to view it or print it out on hardcopy.

    What went hairwire? Some of the headers appears after the actual data.

    Do let me know if you have better suggestion In fact, there are a few more fields that are not directly related to the actual data to be included in the report. That's why it makes the whole report so complicated.
    Last edited by tingshen; 02-22-07 at 13:14.

Posting Permissions

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