CODEWITHSUNDEEP
mysqlinsert
Sanjay Khatri
Sanjay Khatri

Reputation: 4211

MySql insert data in multiple table at a time

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 ideas about it?

Upvotes: 0

Views: 2567

Answers (3)

Hendra Jaya
Hendra Jaya

Reputation: 1628

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.

Upvotes: 0

Praveen kalal
Praveen kalal

Reputation: 2129

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.

Upvotes: 0

Daniel Vassallo
Daniel Vassallo

Reputation: 344311

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.

Upvotes: 1

Related Questions