CODEWITHSUNDEEP
mysqlalter-tablealter
flavianatill
flavianatill

Reputation: 484

Is it possible to append the columns (structure and content) of one mysql table to another?

I would like to copy the structure and the content of one mysql table to another, adding all of the columns and values of that table to the already existing ones in the other table.

I could do it manually, but since I'm talking about a large amount of columns, it would be great if there were some sort of ALTER statement to help me do that.

EDIT:

To explain myself better:

I first need to add the columns contained in table B (column_name, data_type) to table A (which already has its own set of columns). Once that is done, I can copy the content, which is easy.

I guess the real question is: is there a way to add the columns contained in table B to another table (table A) which has columns of its own?

Upvotes: 1

Views: 701

Answers (2)

Amina
Amina

Reputation: 90

To build on flavianatill's second solution, it seems to me that the export/import step is not needed. If I understand the problem correctly, the following one-liner should do it. 

CREATE TABLE IF NOT EXISTS merged_table AS (SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id);

Sorry, I would have put this in a comment but I lack the reputation!

Upvotes: 1

Simon at The Access Group
Simon at The Access Group

Reputation: 7025

This will copy all data from a source table to a target table. You can specify which columns should go to which. by changing the names of targetColumn.. and sourceColumn....

INSERT INTO targetTable (
    targetColumn1
    targetColumn1
    targetColumn1
....
    targetColumnN
) 
SELECT
    sourceColumn1
    sourceColumn1
    sourceColumn1
....
    sourceColumnN
FROM sourceTable

You can also create sourceTable by doing

CREATE TABLE targetTable LIKE sourceTable

EDIT A method to pull all data from sourceTable to targetTable, however removing targetTable if it exists

DROP TABLE IF EXISTS targetTable;
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

EDIT If you need to keep old data, you may need to remap it but you can merge in other tables

CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;
INSERT INTO targetTable ( fieldsToInsertTo ) SELECT fieldsToSelectFrom FROM oldTargetTable ON DUPLICATE KEY ......;
DROP TABLE IF EXISTS oldTargetTable;
RENAME TABLE targetTable TO oldTargetTable;

This will however potentially either require ON DUPLICATE KEY UPDATE ..... logic, or simply INSERT IGNORE on the second if you are happy throwing away any PRIMARY/UNIQUE key conflicting rows. This assumes you have sourceTable you want to copy and merge with data from oldTargetTable. The table targetTable is just a temporary name.

If you wanted to prefer data from the old table then just swap the order you perform the INSERTs of course

Upvotes: 1

Related Questions