How to insert data in different tables using MySql database?

Question

Normally I insert data in MySql in one table (sales) example.

string query = "INSERT INTO sales (price, user, date) " +
                             "VALUES(" +
                             "'" + txtQuant.Text + "'," +
                             "'" + txtLog.Text + "'," +
                              "NOW())";

But now I change my mind and decided to separately create another table which will I name settings for column price. this is my first time to do this so I have no idea what to do.

I tried this.

string query = "INSERT INTO sales (user, date), settings (price) " +
                             "VALUES(" +
                             "'" + txtLog.Text + "'," +
                              "NOW())", "'" + txtQuant.Text + "'";

but it doesn't work.

@Jayseer If you separate the statements with a semi-colon, you can execute them as "one query". Code: `INSERT INTO tbl(field, field) VALUES(val, val); INSERT INTO tbl2(field2, field2) VALUES(val2, val2);` — Ajo Koshy, Apr 09 '13 at 09:28

score 1 · Accepted Answer · answered Apr 09 '13 at 09:26

1

just do it with 2 queries

INSERT INTO sales (user, date) values('user', now()); 
INSERT INTO settings (price) values(100);

answered Apr 09 '13 at 09:26

Bobby Stenly

1,260
3
13
23

score 1 · Answer 2 · edited May 23 '17 at 10:32

1

MySQL doesn't support inserting into multiple tables in a single query (see sql - insert into multiple tables in one query).

You can place multiple queries in a single transaction to ensure that they are either both successful or neither. The above question has an example of this.

edited May 23 '17 at 10:32

Community

1
1

answered Apr 09 '13 at 09:27

Vindicare

213
1
12

score 0 · Answer 3 · answered Apr 09 '13 at 09:26

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

write this as two queries and execute them as a batch
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

use those queries

$query1 ="INSERT INTO sales (user, date) values(.....)"; 
$query2 ="INSERT INTO settings (price) values(......)";

Ja͢ck · Answer 4 · 2013-04-09T09:34:04.473

You have to separate the two queries:

INSERT INTO sales (user, date) VALUES ( ... )

Followed by

INSERT INTO settings (price) VALUES ( ... )

If sales has an auto_increment primary key, you can use LAST_INSERT_ID() in the second query to reference sales.id after the insertion.

INSERT INTO settings (sale_id, price) VALUES (LAST_INSERT_ID(), ... )

Note that correlated queries like these are best put inside a transaction.

How to insert data in different tables using MySql database?

4 Answers4