Extract some columns data from one table and insert into multiple tables

Question

I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables

• tbl_1: id, name, lastname
• tbl_2: id, name
• tbl_3: id, lastname

I need to get the values from tbl_1 and then insert in tbl_2 and tbl_3 meaning tbl1.name goes to tbl_2.name and tbl_1.lastname goes to tbl3.lastname. Is this possible using SQL queries?

I'm using MySQL latest

Answer 1

Assuming that you are preserving the id assocaited with each name and lastname, run this:

INSERT INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
INSERT INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If tabless tbl_2 and tbl_3 already have data and you wish to overwrite it, run this:

REPLACE INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
REPLACE INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If you are not preserving the ID, then run this:

INSERT INTO tbl_2 (name) SELECT name FROM tbl_1;
INSERT INTO tbl_3 (lastname) SELECT lastname FROM tbl_1;