SQL insert, get inserted id and insert again

Question

I need to insert a row to 2 tables with matching ids. When inserting to groups table, the id is auto increased.

I want to insert a new row in 'groups' table, get the id from the inserted row and then use it to insert a new row to the 'users_in_groups' table.

What I have:

INSERT INTO groups (name, owner_id ) VALUES ('groupname1', 'userid1'); INSERT INTO users_in_groups (user_id, group_id) VALUES ('userid1', group_id_from_above);

I tried

INSERT INTO groups (name, owner_id ) OUTPUT Inserted.id VALUES ('group1', 'user1');

But I do not know how to make the 2 'insert' work on 1 query. Is it even possible?

Are you using MySQL or SQL Server? The OUTPUT syntax is SQL Server. — Gordon Linoff
– Gordon Linoff, Commented May 2, 2020 at 12:04

GMB · Accepted Answer · 2020-05-02 12:03:56Z

2

In MySQL, you can use LAST_INSERT_ID():

INSERT INTO groups (name, owner_id ) VALUES ('groupname1', 'userid1'); INSERT INTO users_in_groups (user_id, group_id) VALUES ('userid1', LAST_INSERT_ID());

answered May 2, 2020 at 12:03

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

WhiteHatMan Over a year ago

That gets the id of the current table to insert, if I am not mistaken.

GMB Over a year ago

@WhiteHatMan: this inserts into users_in_groups the id generated when inserting into groups - which is how I understood your question.

Collectives™ on Stack Overflow

SQL insert, get inserted id and insert again

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related