Question :
I’ve got a little issue and wonder if anyone has come across it themselves or seen a solution.
In one of my reporting scripts, it creates a temporary copy of a table before inserting some rows in order to a bulk update on the source table. To create a copy I used the SQL
CREATE TEMPORARY TABLE myTable LIKE source.myTable;
Which works fine, apart from one table which is partitioned. MySQL can’t create temp tables with partitions.
I can’t do the alternative which would be to do…
CREATE TEMPORARY TABLE myTable SELECT * from source.myTable;
If you do that it a) only creates the column definitions to fit the data in the select (i.e. a VARCHAR column will only be defined according to the max length of the column in the select statement) and b) I don’t want to copy all 4 million rows simply to truncate them in the next statement!
Is the only other option to create it manually from the information schema? That really feels like using a sledgehammer to crack a walnut, surely there must be a simpler way to do it? Not too mention a lot of work.
Answer :
CREATE TEMPORARY TABLE myTable
SELECT * from source.myTable
LIMIT 0; -- Add this
Still creates the temp table, but puts no rows in it.
You can create a non temporary copy of the original table first.
CREATE TABLE intermediate_table LIKE my_table;
It won’t have any records so altering it should be quick:
ALTER TABLE intermediate_table REMOVE PARTITIONING;
Then create your temporary table:
CREATE TEMPORARY TABLE tmp_table LIKE intermediate_table;
Then just drop the intermediate:
DROP TABLE intermediate_table;
Now you can play around with your temporary table without worrying of leaving any permanent table behind if your script halts, which I assume is the reason why we use temporary tables instead of permanent ones.
Why don’t you do this in two steps instead?
CREATE TEMPORARY TABLE myTable LIKE db1.new_table;
Insert into db1.myTable (col1, col2, col3)
select col11, col22, col33
from db1.new_table
where id > 10 and id <20 ;
Then you don’t have to transfer 4 million rows, only the rows you need