#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    56
    Rep Power
    11

    Copy data from one table to another with extra fields


    I have 2 database tables. One that is populated from the other using this statement:

    Code:
    INSERT INTO savedConfigsDeleted SELECT * FROM savedConfigs WHERE UNIX_TIMESTAMP() > dateInactive AND probabilityClosing = 0 AND groupId = 38
    This works when the tables are identical, but I want to have 2 new fields in the savedConfigsDeleted table for deletedUser and deletedDate. How can I do this without explicitly mentioning the fields? The 2 new fields will always be the last 2 in the table. I have looked around and tried this, but it is wrong:

    Code:
    INSERT INTO savedConfigsDeleted VALUES ((SELECT * FROM savedConfigs WHERE UNIX_TIMESTAMP() > dateInactive AND probabilityClosing = 0 AND groupId = 38), 1, 1365149703)
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Code:
    INSERT 
      INTO savedConfigsDeleted 
    SELECT savedConfigs.*
         , 1
         , 1365149703 
      FROM savedConfigs 
     WHERE UNIX_TIMESTAMP() > dateInactive 
       AND probabilityClosing = 0 
       AND groupId = 38

    Comments on this post

    • Jyncka agrees : I love that sql can do that, so useful.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    56
    Rep Power
    11
    Perfect, thank you.

IMN logo majestic logo threadwatch logo seochat tools logo