Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28

    Unanswered: Insert Statement

    Hi,
    Is there anyway to INSERT a record into the first row of a table ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope

    that's because there is no such thing as the first row of a table -- all the rows are equal

    what is it that you are really trying to do, and why? can you give examples?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28
    Quote Originally Posted by r937
    nope

    that's because there is no such thing as the first row of a table -- all the rows are equal

    what is it that you are really trying to do, and why? can you give examples?
    Hello and thanks for the response.

    I am trying to send a temp table into an outfile, after inserting a header record.
    The insert is plain and easy but I just want it to be at the top and not somewhere in the 33rd thousandth row.
    Any ideas pls?
    thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28
    Quote Originally Posted by r937
    use ORDER BY
    Already tried that,
    ORDER BY MATERIAL_NBR....
    But MATERIAL_NBR may have alpha numeric characters.

    A00198,
    material_nbr,
    P0021,
    ZZZ900... AND SO ON.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ORDER BY special_condition_for_header
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28
    Quote Originally Posted by r937
    ORDER BY special_condition_for_header
    Just tried that didnt work,mysql doesnt seem to recognise 'special_condition_for header'.

    Anyways i found a way out.

    To add a header to the outfile the best way to do this and ensuring that its at the top is to use a UNION statement.

    e.g.

    SELECT 'MATERIAL_NBR', 'MATERIAL_TYPE'
    UNION
    SELECT MATERIAL_NBR, MATERIAL_TYPE INTO OUTFILE 'C:\TEST\TEST.TXT'
    FIELDS TERMINATED BY '|'
    LINES TERMINATED BY '\n\r' FROM TABLE MATERIALS;

    Works like a charm.
    Thanks for your help..

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by macjoubert
    mysql doesnt seem to recognise 'special_condition_for header'.
    of course not, that was for you -- you were supposed to write a special condition that recognizes the header row

    i hate to break it to you, but without an ORDER BY clause, that UNION query is not guaranteed to give you the header first -- it will give you the header row first only until the one time it doesn't

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

  9. #9
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28

    Lightbulb

    Quote Originally Posted by r937
    of course not, that was for you -- you were supposed to write a special condition that recognizes the header row

    i hate to break it to you, but without an ORDER BY clause, that UNION query is not guaranteed to give you the header first -- it will give you the header row first only until the one time it doesn't

    Aha I found a solution for that too my friend.
    After the union statement if the SELECT MATERIAL_NBR etc is enclosed in parantheses ORDER BY doesnt alter the header row!!

    e.g.

    SELECT 'MATERIAL_NBR', 'MATERIAL_TYPE'
    UNION
    (SELECT MATERIAL_NBR, MATERIAL_TYPE INTO OUTFILE 'C:\TEST\TEST.TXT'
    FIELDS TERMINATED BY '|'
    LINES TERMINATED BY '\n\r' FROM TABLE MATERIALS ORDER BY MATERIAL_NBR);

    Will yield the same result!!
    Last edited by macjoubert; 02-21-06 at 12:49.

Posting Permissions

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