Adding data to two tables at the same time

Question

I have database with a table called members, which stores data for my admin,client,technicians all in one.But now I want to break this table into three separate tables as members and techs.members table has the common fields for both all admin,client and technician. Technicians have more data than the other two.These extra data I want to store in another table called techs.In members table I have a field as ID and in techs table there is a field as tech ID.What I want is when I insert a technician data for members table that particular ID should get inserted to techs table tech ID column

What RDBMS are you using (MySql, SQL Server, Oracle, ...)? And how is ID column defined in members table? Is it identity (auto_increment) column? — peterm
– peterm, Commented Jul 30, 2013 at 4:21
can you provide your member table field and other table field,so we can easily create your query. — Manish Sharma
– Manish Sharma, Commented Jul 30, 2013 at 4:50
I am using MySql.My members table fields are Id,Name,Username,Password,Level,Address,Phone.techs table fields are techID,price,company,comments,Rate,qualifications.Id in members table is primary key not null auto_increment — clarkson
– clarkson, Commented Jul 30, 2013 at 5:42

Alex · Accepted Answer · 2013-07-30 11:52:05Z

3

For mysql (with auto_increment id in members table) it can be:

START TRANSACTION;
insert into members ...
insert into admin ... (id, ...) values (last_insert_id(), ...);
COMMIT;

UPDATE
After you provided structure an example is:

    START TRANSACTION;
    insert into members (Name, Username, Password, Level, Address, Phone)
       values ('name','username','password','level','address','12345')
    insert into techs (techID, price, company, comments, rate, qualification)
       values (last_insert_id(), 10, 'company', 'comment', 1, 'qualification');
    COMMIT;

Members.id will be created automatically because it's auto_increment field.

edited Jul 30, 2013 at 11:52

answered Jul 30, 2013 at 4:27

Alex

11.6k7 gold badges39 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

Adding data to two tables at the same time

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related