Mysql insert into 2 tables

Question

I want to make a insert into 2 tables

visits:

visit_id int | card_id int

registration:

registration_id int | type enum('in','out') | timestamp int | visit_id  int

I want something like:

INSERT INTO `visits` as v ,`registration` as v
(v.`visit_id`,v.`card_id`,r.`registration_id`, r.`type`, r.`timestamp`, r.`visit_id`) 
VALUES (NULL, 12131141,NULL, UNIX_TIMESTAMP(), v.`visit_id`);

I wonder if its possible

Answer 1

It's not possible with one query as INSERT can only insert data to one table in mysql. You can either

1. write this as two queries and execute them as a batch
2. create a stored procedure that would execute two insert command

You can wrap those inserts in transaction if you need to make sure that both queries will write the data.

Answer 2

It seems like the problem you are trying to solve is to get the auto-increment value from the "visits" row to insert into "registration". Am I right?

If so, you can just use the LAST_INSERT_ID() function like this:

INSERT INTO `visits` (`visit_id`,`card_id`) 
VALUES (NULL, 12131141);
INSERT INTO `registration` (`registration_id`, `type`, `timestamp`, `visit_id`) 
VALUES (NULL, 'in', UNIX_TIMESTAMP(), LAST_INSERT_ID());