How to transfer table's data to new table?

Question

I need to transfer the data of 2 tables into the new table, here is the simplified table.

Table 1

user_id_slot_1 | user_id_slot_2 | some_column | some_column_2

Table 2

user_id_slot_1 | user_id_slot_2 | some_column | some_column_2

Note:

• Data from table 1 and 2 that I need to transfer/copy is almost identical.

• user_id_slot_1 and user_id_slot_2 either one of them should be empty/null.

• Column's names are different from the actual database.

New Table

id | user_id | some_column | some_column_2

How can I transfer the data from Table 1 and 2?

How can I merged the column user_id_slot_1 and user_id_slot_2 into one and transfer it to user_id.

UPDATE:

I do not need to transfer/copy the ids of the Table 1 and 2, New Table needs to auto increment it.

Answer 1

Assuming that the new table already exists, you can use INSERT INTO ... SELECT to move the data. I have used UNION ALL here under the assumption that you don't want to remove duplicates between the two source tables should they occur.

INSERT INTO new_table (`user_id`, `some_column`, `some_column_2`)
SELECT COALESCE(user_id_slot_1, user_id_slot_2),
       some_column,
       some_column2
FROM table1
UNION ALL
SELECT COALESCE(user_id_slot_1, user_id_slot_2),
       some_column,
       some_column2
FROM table2

Notes: The COALESCE(user_id_slot_1, user_id_slot_1) term in the above query will choose user_id_slot_1 if it be not NULL otherwise it will choose user_id_slot_2. This should be fine assuming that one and only one will be non NULL for every record.

Assuming that the new_table table has its id column set to auto increment, then MySQL will handle assigning these values for you. All you need to do is omit a value for id in new_table and MySQL will handle the rest.

You should create the new table using something like this:

CREATE TABLE new_table (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    some_column VARCHAR(50) NOT NULL,
    some_column_2 VARCHAR(50) NOT NULL
)

Answer 2

Is this what you want?

create table3 as
    select id, coalesce(user_id_slot_1, user_id_slot_2) as user_id,
           some_column, some_column_2
    from table1
    union all
    select id, coalesce(user_id_slot_1, user_id_slot_2) as user_id,
           some_column, some_column_2
    from table2;