Menu
I'm trying to populate a new table with records from 2 other tables.
period_states is new/empty
period_states
id
period_id
sla_id
periods - contains 15 records
periods
id
slas - contains 84 records
slas
id
I need to populate period_states with each existing sla_id having each exiting period_id. So, ultimately there should be 1260 records in period_states.
Any idea how to automate this? It would be a nightmare to populate manually...
Thank you in advance!!
812
To insert records from multiple tables, use INSERT INTO SELECT statement. Here, we will insert records from 2 tables.
Multiple tables can be merged by columns in SQL using joins. Joins merge two tables based on the specified columns (generally, the primary key of one table and a foreign key of the other).
INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.
If the id column of period states is defined as AUTO_INCREMENT, then this should work:
INSERT INTO period_states (period_id, sla_id)
SELECT p.id AS period_id
, s.id AS sla_id
FROM periods p
CROSS
JOIN slas s
And here's an example of one way to supply a value for the id column:
INSERT INTO period_states (id, period_id, sla_id)
SELECT @myid := @myid + 1 AS id
, p.id AS period_id
, s.id AS sla_id
FROM periods p
CROSS
JOIN slas s
CROSS
JOIN (SELECT @myid := 0) m
INSERT INTO period_states
(period_id, sla_id)
SELECT periods.id, slas.id
FROM periods
CROSS JOIN slas
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
