I have two tables with identical structure except for one column... Table 2 has an 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 to the difference in the number of columns.
I have two questions:
SET @sql = CONCAT( 'INSERT INTO <table_name> (', ( SELECT GROUP_CONCAT( CONCAT('`',COLUMN_NAME,'`') ) FROM information_schema.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With