MYSQL: how to copy the entire row from one table to another in mysql with a second table having one additional column?

Question

I have two tables with the same structure, except for one column ... In table 2 there is an additional column into which I would insert 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 indicating the difference in the number of columns.

I have two questions:

• How do I get around this?
• and how to add a value for an extra date column (CURRENT_DATE ()) in table2 in the same expression?

Answer 1

To clarify the answer from Zed and respond to your comment:

 INSERT INTO dues_storage SELECT d.*, CURRENT_DATE() FROM dues d WHERE id = 5; 

See TJ Commentary on Crowder

Answer 2

The safest way to do this is to fully specify the columns for both insert and extract. There is no guarantee (for the application) that any of them will be what you think.

 insert into dues_storage (f1, f2, f3, cd) select f1, f2, f3, current_date() from dues where id = 5; 

If you are worried that you need to modify several PHP pages that do this (as you seem to point out in the comment to another answer), this is ripe for the stored procedure. Thus, all of your PHP pages simply call a stored procedure with (for example) only the identifier for copying and control the actual copying process. Thus, there is only one place where you need to maintain the code, and, in my opinion, the DBMS is the right place for this.

Answer 3

 INSERT INTO dues_storage SELECT field1, field2, ..., fieldN, CURRENT_DATE() FROM dues WHERE id = 5; 

Answer 4

Hope this helps someone ... Here is a little PHP script I wrote if you need to copy some columns but not others, and / or the columns are not in the same order in both tables. As long as the columns are called the same, this will work. Therefore, if table A has [userid, handle, something] and tableB has [userID, handle, timestamp], then you would "SELECT userID, handle, NOW () as the timestamp FROM tableA", then get the result of this and pass the result as the first parameter of this function ($ z). $ toTable is the string name for the table you are copying to, and $ link_identifier is the db you are copying to. It is relatively fast for small data sets. It is not recommended that you try to move more than several thousand lines at a time this way in production settings. I use this primarily to back up the data collected during the session when the user logs out and then immediately flushes the data from live db to keep it thin.

  function mysql_multirow_copy($z,$toTable,$link_identifier) { $fields = ""; for ($i=0;$i<mysql_num_fields($z);$i++) { if ($i>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=>$as) { $a[$key] = addslashes($as); next ($a); } if ($c>0) { $q .= ","; } $q .= "('".implode(array_values($a),"','")."')"; $c++; } $q .= ";"; $z = mysql_query($q,$link_identifier); return ($q); } 

Answer 5

Alternatively, you can use Internal Queries to do this.

 SQL> INSERT INTO <NEW_TABLE> SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM <OLD_TABLE>); 

Hope this helps!

Answer 6

Just wanted to add this little snippet that works great for me.

INSERT INTO your_target_table SELECT * FROM your_rescource_table WHERE id = 18;

And while I'm in, he screams loudly at Sequel Pro, if you are not using it, I highly recommend downloading it ... making life easier.

Answer 7

I tried what you said, but I get a message saying that there is a problem with the where clause

here is what i tried:

  INSERT INTO `seller` SELECT FROM `seller_test` WHERE `s_code`=11; 

Mistake:

 > #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use > near 'FROM `seller` WHERE `s_code`=11' at line 1 

but when I added * after the SELECT keyword and the query became something like this:

 INSERT INTO `seller` SELECT ***** FROM `seller_test` WHERE `s_code`=11; 

I get a good result so try adding * after the SELECT keyword