MySql insert data in multiple table at a time

Question

hi,

i have a query regarding to insert data in multiple table..

i have a two tables. one is item table and second is field table.

itemid from item table reference in the field table.

i want to insert data in both table with one query at a time.

any idea about it.

Answer 1

You should consider using two INSERT operations wrapped in a transaction. The transaction will make multiple operations act atomically. Note that you will need to use the InnoDB storage engine in MySQL to use transactions.

Answer 2

firstly you have to insert the data in one table than after you will received lastinsertid using mysql function thats primary key of first table. using this value you can insert data in another table.

Answer 3

Maybe a trigger will help. I'll give you an example to do that.

Suppose you have table ITEM with ITEM_ID field like this :

ITEM
---
ITEM_ID (PK)

Another table is ITEM_DETAIL with some other fields :

ITEM_ID
---
ITEM_ID (PK auto_increment)
ITEM_NAME

Then construct a trigger which will be invoked when an insertion happens. Like this :

CREATE TRIGGER `ITEM_DETAIL_INSERTION` AFTER INSERT ON `ITEM_DETAIL`
FOR EACH ROW
BEGIN
INSERT INTO `ITEM` (`ITEM_ID`) VALUES (NEW.ITEM_ID);
END;

This trigger will be fired when you insert into ITEM_DETAIL table. This allows you not to explicitly write additional code to insert into ITEM table. Note that you should modify your code to insert into ITEM_DETAIL.

Another advantages of using trigger is this trigger get fired anytime and anywhere insertion happens on ITEM_DETAIL. Perhaps a stored procedure, bulk insert, etc.