Inserting value in one table for all the rows in another table

Question

I want to write a sql script, which inserts into table some value for all the ids in some other table.

create table person
(
    id int(11) not null auto_increment,
    name varchar(255),
    primary key (id)
);

insert into person
values (null, 'John'), (null, 'Sam');

select * from person;

id | name
----------
1  | John
2  | Sam

create table phone_details
(
    id int(11) not null auto_increment,
    person_id int(11),
    phone int(11),
    constraint person_ibfk_1 foreign key (person_id) references person (id) on delete no action,
    primary key (id)
);

Now, in the phone_details table, I want the following :

id | person_id | phone
----------------------------
1  |    1      | 9999999999
2  |    2      | 9999999999

How do I do that ? Currently, I am using Java to write this one time script, but I think there must be a way of doing this in sql.

Answer 1

You can use INSERT INTO ... SELECT syntax:

INSERT INTO phone_details(person_id,phone)
SELECT id, 99999999
FROM person;

Consider storing phone number as VARCHAR.

SqlFiddleDemo