CODEWITHSUNDEEP
mysqlsql
Alex
Alex

Reputation: 6645

Copying data between two tables using ID from third table

How can I copy data from one table (table A) to another (table B) repeating that data for all records in Table A for each ID in table C.

Lets say i have table_a contains 100 records. Table b is empty and has a similar structure to table a (same datatypes, different column names). Table C has 5 records. I want to copy each record in table a into table b 5 times. Each of those times using a id from table c. So table b will end up with 100 x 5 records (500).

Upvotes: 0

Views: 292

Answers (1)

tadman
tadman

Reputation: 211560

You can just use a full join for this:

CREATE TABLE a(id INT);
INSERT INTO a (id) VALUES (1,2,3,4,5,6);

CREATE TABLE b(id INT);
INSERT INTO b (id) VALUES (10,20,30,40,50,60);

CREATE TABLE c (a_id INT, b_id INT) AS SELECT a.id AS a_id, b.id AS b_id FROM a,b;

You can, of course, use INSERT INTO c instead, the same principle applies.

Upvotes: 2

Related Questions