1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

mysql help needed on insert data from one table to another table.

Discussion in 'Programming' started by exodus, Mar 4, 2008.

  1. #1
    INSERT INTO tbl_temp2 (fld_id)
      SELECT tbl_temp1.fld_order_id
      FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
    Code (markup):
    About 6 months ago I found a routine that would select data from one table and insert that data into another table. I need this routine again, but can not find the exact page where it explained how to do it and my memory is bad and can't recall how to do it myself.

    INSERT INTO new_tbl (entry,entry,entry) SELECT old_tbl.entry,old_tbl.entry,old_tbl.entry FROM old_tbl;
    Code (markup):
    I have not done it yet, but do you think the above code would work? I am about to try it and thought to ask if there is a better way of doing it.
     
    exodus, Mar 4, 2008 IP
  2. QiSoftware

    QiSoftware Well-Known Member

    Messages:
    805
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    158
    #2
    QiSoftware, Mar 5, 2008 IP
  3. exodus

    exodus Well-Known Member

    Messages:
    1,900
    Likes Received:
    35
    Best Answers:
    0
    Trophy Points:
    165
    #3
    This is still giving me problems.

    SELECT * INTO backup FROM tbl
    Code (markup):
    The above code is giving me problems. It isn't working for me.
     
    exodus, May 5, 2008 IP
  4. apmsolutions

    apmsolutions Peon

    Messages:
    66
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    insert into TableName1
    Select * from TableName2

    Number of columns in each table need to be the same, otherwise, specify the column names instead of wildcard.
     
    apmsolutions, May 5, 2008 IP
  5. exodus

    exodus Well-Known Member

    Messages:
    1,900
    Likes Received:
    35
    Best Answers:
    0
    Trophy Points:
    165
    #5
    I don't know.. I did this with a local install of mysql and I think maybe it is just host gator that isn't allow it to happen. Going to reinstall a local mysql and phpMyAdmin to run tests. Thanks!
     
    exodus, May 5, 2008 IP
  6. yugolancer

    yugolancer Well-Known Member

    Messages:
    320
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    110
    #6
    It is not your hosting ... rather it is the SQL statement

    SELECT * INTO backup FROM tbl

    You cannot select into ... you can select FROM. very obvious mistake.


    In addition If your tables are EXACTLY the same and they do not have any constraints that would prevent a direct insert, then you may use:

    INSERT INTO tbl (the table you want to copy INTO)
    SELECT * from tbl (the table you want to copy FROM)
    WHERE frield = (some condition)

    This is a clean and easy way to copy data.
     
    yugolancer, Jun 29, 2008 IP
  7. yugolancer

    yugolancer Well-Known Member

    Messages:
    320
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    110
    #7
    Actually i just found some info about your statement and it seems that you were right. There is sucha statement that should create a duplication of certain table and insert all rows.

    SELECT *
    INTO new_table
    FROM my_table

    Sorry for the misleading
     
    yugolancer, Jun 29, 2008 IP