CODEWITHSUNDEEP
mysql
Katie M
Katie M

Reputation: 1131

MySQL Insert Records from Multiple Tables to New Table

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!!

Upvotes: 6

Views: 4246

Answers (4)

hair raisin
hair raisin

Reputation: 2628

INSERT INTO period_states
(period_id, sla_id)
SELECT periods.id, slas.id
FROM periods
CROSS JOIN slas

Upvotes: 4

spencer7593
spencer7593

Reputation: 108370

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

Upvotes: 6

dispake
dispake

Reputation: 3329

Check out the "insert into.... select" http://dev.mysql.com/doc/refman/5.1/en/insert-select.html syntax. ... pretty much like what the other answers have displayed.

Upvotes: 0

Kevin
Kevin

Reputation: 56059

insert into period_states select null, periods.id, slas.id from periods, slas

Upvotes: 2

Related Questions