SQL insert help

inserting into related tables

[ukgimp]

Hello

I have a relational DB and wish to insert into multiple tables records that
are related (from one form), are there any good resources detailing how to do this.

EG

tblX
PK - Xid
field1
field2

tblY
PK - Yid
FK - Xid
field a
field b

Would this be done with an inner join INSERT similar to a SELECT * blah blah
WHERE etc. I have looked across the web but I cannot find this. Does it have a name?

Cheers

Richard

[mavherick]

I'm pretty sure you'll have to use 2 different insert for this one. It might also be easier if your Primary Key Xid for tblX is known prior to the insert as opposed to an automatically generated one. That way you can simply insert with the right values (probably tblX first to preserve data integrity).

[jatar_k]

I think mav's got it,

You will have to insert into your primary table first and then put the row into the secondary so that the foreign key is valid.

[ergophobe]

UKgimp,

Let me give you a few more specifics, though these are PHP/mysql specfic. That seems to be the dominant combo in this forum, so I hope this helps. If you have a DB that has genuine support for foreign keys and all that stuff, there is no doubt a better way. If you're using Mysql, here's the dope

I'm also assuming that the primary keys are auto-increment fields since, if they were values that you were assigning yourself, you wouldn't have the quandry in the first place (is this right?)

1. First do your insert in Table X, which has one and only one auto-increment field, namely your primary key

2. Get the value of the auto-increment field using
$pk_Xid = mysql_insert_id()

3. Do your insert into Table Y, using $fk_Xid=$pk_Xid

Cheers,

Tom

[mavherick]

or if you really want to get fancy:

INSERT INTO tblX (field1, field2) VALUES ($val1, $val2);
INSERT INTO tblY (Xid, fieldA, fieldB) VALUES (LAST_INSERT_ID(), $valA, $valB);

mavherick