How to insert many related records in one sql file

Question

I want to insert many records in 3 tables, businesses, business_categories (business_id), business_details(business_id), I want to insert many data.

as you can see, the data in business_categories and business_details will need to use the business's id, and when inserting data, I still don't know the business id.

in the past, I was writing a ruby script to do that things, but it's pretty slow. and now I want to write a script to generate sql file directly, it can be the fast way.

I would write this.

insert into businesses(name, ..) values ("blabla", ...)
insert into business_categories(business_id, ..) values(? ..)
insert into business_details(business_id, ..) values(? ..)

Is it possible to set the right value to the ? ?

thanks.

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

You could save the last inserted id to a variable and set that value in the following inserts.

https://stackoverflow.com/a/17112962/1165441

This answer has some info that is really good.

edited May 23 '17 at 11:57

Community

1
1

answered Feb 21 '14 at 03:36

edhedges

2,722
2
28
61

I think LAST_INSERT_ID() should work in the second line,but on the 3rd line, last_insert_id changed, it's become the last business_categories's id, what should i do to insert business_details? – dexterdeng Feb 21 '14 at 03:40
1

Assign it to a variable before doing the second insert. – Barmar Feb 21 '14 at 03:42
`set @lastid := last_insert_id()` – Barmar Feb 21 '14 at 03:43
Oh, Thank you, Thank you. It's so great. I didn't know there is variable in mysql . you are so great! – dexterdeng Feb 21 '14 at 03:48

How to insert many related records in one sql file

1 Answers1