MYSQL How to copy an entire row from one table to another in mysql with the second table having one extra column

Question

I have two tables with identical structure except for one column    Table 2 has that additional column in which i would insert the CURRENT DATE    I would like to copy all the values from table1 to table2   if i use  INSERT INTO dues storage SELECT   FROM dues WHERE id 5    it throws an error pointing out to the difference in the number of columns    I have two questions on that    How do I get around this  and how do I add the value for the additional date column  CURRENT DATE    in table2 within this same statement

User · Answer

Alternatively  you can use Inner Queries to do so   SQL gt  INSERT INTO  lt NEW TABLE gt  SELECT   FROM CUSTOMERS WHERE ID IN  SELECT ID FROM  lt OLD TABLE gt      Hope this helps

User · Answer

Just wanted to add this little snippet which works beautifully for me    INSERT INTO your target table SELECT    FROM your rescource table WHERE id   18   And while I m at it give a big shout out to Sequel Pro  if you re not using it I highly recommend downloading it   makes life so much easier

User · Answer

To refine the answer from Zed  and to answer your comment   INSERT INTO dues storage SELECT d    CURRENT DATE   FROM dues d WHERE id   5    See T J  Crowder s comment

User · Answer

The safest way to do it is to fully specify the columns both for insertion and extraction  There s no guarantee  to the application  that either of these will be the order you think they may be   insert into dues storage  f1  f2  f3  cd      select f1  f2  f3  current date   from dues where id   5    If you re worried about having to change many multiple PHP pages that do this  as you seem to indicate in the comment to another answer   this is ripe for a stored procedure  That way  all your PHP pages simply call the stored procedure with  for example  just the ID to copy and it controls the actual copy process  That way  there s only one place where you need to maintain the code  and  in my opinion  the DBMS is the right place to do it

User · Answer

SET  sql    CONCAT   INSERT INTO  lt table name gt                     SELECT GROUP CONCAT  CONCAT     COLUMN NAME                     FROM information schema columns              WHERE table schema    lt database name gt                  AND table name    lt table name gt                  AND column name NOT IN   id             SELECT                    SELECT GROUP CONCAT CONCAT     COLUMN NAME                FROM information schema columns          WHERE table schema    lt database name gt              AND table name    lt table source name gt              AND column name NOT IN   id             from  lt table source name gt  WHERE  lt testcolumn gt     lt testvalue gt         PREPARE stmt1 FROM  sql   execute stmt1    Of course replace  lt   values with real values  and watch your quotes

User · Answer

Hope this will help someone    Here s a little PHP script I wrote in case you need to copy some columns but not others  and or the columns are not in the same order on both tables  As long as the columns are named the same  this will work  So if table A has  userid  handle  something  and tableB has  userID  handle  timestamp   then you d  SELECT userID  handle  NOW   as timestamp FROM tableA   then get the result of that  and pass the result as the first parameter to this function   z    toTable is a string name for the table you re copying to  and  link identifier is the db you re copying to  This is relatively fast for small sets of data  Not suggested that you try to move more than a few thousand rows at a time this way in a production setting  I use this primarily to back up data collected during a session when a user logs out  and then immediately clear the data from the live db to keep it slim    function mysql multirow copy  z  toTable  link identifier                 fields                   for   i 0  i lt mysql num fields  z   i                      if   i gt 0                         fields                                            fields    mysql field name  z  i                              q    INSERT INTO  toTable   fields  VALUES                c   0              mysql data seek  z 0     critical reset in case  z has been parsed beforehand                while   a   mysql fetch assoc  z                     foreach   a as  key  gt  as                         a  key    addslashes  as                       next   a                                     if   c gt 0                         q                                            q         implode array values  a                                c                               q                      z   mysql query  q  link identifier               return   q

User · Answer

INSERT INTO dues storage SELECT field1  field2       fieldN  CURRENT DATE   FROM dues WHERE id   5

[mysql] MYSQL: How to copy an entire row from one table to another in mysql with the second table having one extra column?

Examples related to mysql