Insert data into joined tables

Question

I can select and join, but I can't insert with these below tables in the same time. How to insert data into joined tables?

company(cid, comp_name)
role(rid, role_name)
user(uid, user_name, cid, rid)

cid,rid and uid are auto incremented primary keys.

I want to accept user_name,comp_name,role_name from the front end app and enter the user_name with corresponding cid and rid in user table. I am able to fetch the data after join from the user table but i don't know how to insert.

I have written a query:

INSERT INTO user ( user_name, cid, rid)
SELECT c.cid, r.rid FROM company c
    JOIN user u
    join role r ON c.cid = u.cid and r.rid=;

Please help. Thanks in advance

score 0 · Answer 1 · edited May 09 '18 at 09:49

If you want to insert in three different tables at the same time. You have to make three different queries.

Example:

INSERT INTO user ( user_name, cid, rid) values (/*Variables for user_name, cid, rid*/)
INSERT INTO company( cid, comp_name) values (/*Variables for cid, comp_name. cid must have been same as cid above then store it in a variable and use it everywhere*/)
INSERT INTO role( rid, role_name) values (/*Variables for role*/)

If it is not what you want then please explain your questions clearly.

Or you can use Scop_Identity() i-e "insert into tbl1 values (@name, @age, @address)

insert into tbl2 values (scope_identity(), @roleid)"

Insert data into joined tables

1 Answers1