如何将表的数据转移到新表?

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

我需要将两个表的数据转移到新表中，这是简化表。

Table 1

表1

user_id_slot_1 | user_id_slot_2 | some_column | some_column_2

Table 2

表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.

我需要传输/复制的表1和表2的数据几乎相同。
user_id_slot_1 and user_id_slot_2 either one of them should be empty/null.

user_id_slot_1和user_id_slot_2中的任何一个都应该为空/空。
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?

如何从表1和表2中传输数据?

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

如何将列user_id_slot_1和user_id_slot_2合并为一个并将其传输到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.

我不需要转移/复制表1和表2的id，新表需要自动递增。

2 个解决方案

#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…选择移动数据。我在这里使用UNION ALL，假设您不希望在两个源表之间删除重复。

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.

注意:在上面的查询中，合并(user_id_slot_1, user_id_slot_1)将选择user_id_slot_1，否则它将选择user_id_slot_2。假设对于每条记录，1且只有1是非空的，这应该没问题。

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.

假设new_table表的id列设置为自动递增，那么MySQL将为您分配这些值。您只需在new_table中省略id值，MySQL将处理其余的。

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
)

#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;

#1