SQL Multiple Tables Insertion

Question

So I'm new to the use of multiple tables. Prior to today, 1 table suited my needs (and I could probably get away with using 1 here as well).

I'm creating a plugin for a game I play but I'm using a MySQL database to store all the information. I have 3 tables, Players, Warners and Warns. Warns has 2 foreign keys in it (one referencing to Players and the other to Warners).

At the moment I need to do 3 queries. Add the information to Players & Warners, and then to Warns. Is there a way I can cut down the amount of queries and what would happen if I were to just omit the first 2 queries?

Query Examples:

INSERT INTO slimewarnsplayers VALUES ('123e4567-e89b-12d3-a456-426655440000', 'Spedwards');

INSERT INTO slimewarnswarners VALUES ('f47ac10b-58cc-4372-a567-0e02b2c3d479', '_Sped');

INSERT INTO slimewarnswarns VALUES ('', '123e4567-e89b-12d3-a456-426655440000', 'f47ac10b-58cc-4372-a567-0e02b2c3d479', 'spamming', 'medium');

Tables:

CREATE TABLE IF NOT EXISTS SlimeWarnsPlayers (
    uuid VARCHAR(36) NOT NULL,
    name VARCHAR(26) NOT NULL,
    PRIMARY KEY (uuid)
);

CREATE TABLE IF NOT EXISTS SlimeWarnsWarners (
    uuid VARCHAR(36) NOT NULL,
    name VARCHAR(26) NOT NULL,
    PRIMARY KEY (uuid)
);

CREATE TABLE IF NOT EXISTS SlimeWarnsWarns (
    id INT NOT NULL AUTO_INCREMENT,
    pUUID VARCHAR(36) NOT NULL,
    wUUID VARCHAR(36) NOT NULL,
    warning VARCHAR(60) NOT NULL,
    level VARCHAR(60) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (pUUID) REFERENCES SlimeWarnsPlayers(uuid),
    FOREIGN KEY (wUUID) REFERENCES SlimeWarnsWarners(uuid)
);

Rahul · Accepted Answer · 2014-11-21T14:15:39.663

Is there a way I can cut down the amount of queries?

NO, I don't see that. From your posted INSERT statements (as depicted below) it's clear that those are 3 different tables and you are inserting different data to them. so, you will have to perform the INSERT operation separately for them.

INSERT INTO slimewarnsplayers 

INSERT INTO slimewarnswarners 

INSERT INTO slimewarnswarns

Another option would be (May not be considered good), creating a procedure which will accept the data and table name and create a prepared statement/dynamic query to achieve what you are saying. something like (A sample pseudo code)

create procedure sp_insert(tablename varchar(10), data1 varchar(10), 
                                                  data2 varchar(10))
as
begin
--dynamic query here
INSERT INTO tablename VALUES (data1, data2);
end

To explain further, you can then call this procedure from your application end passing the required data. Do note that, if you have a Foreign Key relationship with other table then you will have to catch the last inserted key from your master table and then pass the same to procedure.

That are two entities, one relation (which references the other two inserts), the correct foreign keys, so it needs three inserts. — lx42.de, Nov 21 '14 at 14:10
@lx42.de, absolutely correct. Though using dynamic query is also possible (you will have to catch the last inserted key value from master table) but I wouldn't go for it; but worth mentioning once. — Rahul, Nov 21 '14 at 14:17
I didnt catch (the benefit) of your idea, because you can get the latest insert id and not the last two ones (http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id) you have to insert, mem=LAST_INSERT_ID(), insert, insert(mem, LAST_INSERT_ID(), ...) resulting in four queries. — lx42.de, Nov 21 '14 at 14:57

score 2 · Answer 2 · edited May 23 '17 at 11:58

No, you can't insert into multiple tables in one MySQL command. You can however use transactions.

BEGIN;
INSERT INTO slimewarnsplayers VALUES(.....);
 last_id = LAST_INSERT_ID()
INSERT INTO SlimeWarnsWarners VALUES(last_id, ....);
INSERT INTO SlimeWarnsWarns VALUES(last_id, ....);
COMMIT;

I would also take a look at http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

and this post MySQL Insert into multiple tables? (Database normalization?)

SQL Multiple Tables Insertion

2 Answers2