I'm trying to populate a new table with records from 2 other tables.
我正在尝试用其他2个表中的记录填充新表。
period_states is new/empty
period_states是new / empty
period_states
id
period_id
sla_id
periods - contains 15 records
期间 - 包含15条记录
periods
id
slas - contains 84 records
slas - 包含84条记录
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.
我需要使用每个现有的sla_id填充period_states,每个sla_id都有一个exiting period_id。所以,最终在period_states中应该有1260条记录。
Any idea how to automate this? It would be a nightmare to populate manually...
知道如何自动化吗?手动填充将是一场噩梦......
Thank you in advance!!
先感谢您!!
4 个解决方案
#1
4
INSERT INTO period_states
(period_id, sla_id)
SELECT periods.id, slas.id
FROM periods
CROSS JOIN slas
#2
6
If the id
column of period states is defined as AUTO_INCREMENT
, then this should work:
如果句点状态的id列定义为AUTO_INCREMENT,那么这应该有效:
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:
这是一个为id列提供值的方法的示例:
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
#3
2
insert into period_states select null, periods.id, slas.id from periods, slas
#4
0
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.
查看“插入....选择”http://dev.mysql.com/doc/refman/5.1/en/insert-select.html语法。 ......几乎与其他答案所显示的一样。
#1
4
INSERT INTO period_states
(period_id, sla_id)
SELECT periods.id, slas.id
FROM periods
CROSS JOIN slas
#2
6
If the id
column of period states is defined as AUTO_INCREMENT
, then this should work:
如果句点状态的id列定义为AUTO_INCREMENT,那么这应该有效:
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:
这是一个为id列提供值的方法的示例:
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
#3
2
insert into period_states select null, periods.id, slas.id from periods, slas
#4
0
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.
查看“插入....选择”http://dev.mysql.com/doc/refman/5.1/en/insert-select.html语法。 ......几乎与其他答案所显示的一样。