Insert into two related tables

[Brian Wolfe]

Hey there, I'd like to insert into two tables simultaneously, where the value of the first column in the first table is the same as the value of the second column in the second table. Here's an example of what I'm trying to achieve:

Table 1
pri_ID, some_data
10,words
11,something

Table 2
sec_ID, pri_ID, other_data
1,10,morewords
2,11,somethingelse

Both pri_ID and sec_ID are auto increments, and some_data/more_data are inserted from php. The whole statement will be wrapped in ADOdb functions.

$DB->BeginTrans();
$DB->Execute("SQL STATEMENT GOES HERE");
$DB->CommitTrans();
 

Please let me know if I need to give any more info. Thanks for your time!

[Rabbit]

You can't do it simultaneously. You insert into the primary key table and then insert into the foreign key table.

[Brian Wolfe]

Just to make sure: two separate statements, with the second one calling the primary key of the first? Is there an optimal way to do that during the php function that you know of?

[ck9663]

Yes, two separate statements. It is recommended to do it all in sql server for you to keep the consistency of the relationship.

Enclose your insert statements in a transaction so that if the other insert fails, you can rollback the previous one. You also need to consider to make sure your sequence of auto-number revert back to the value before the fail.

Now, to make sure that you insert only those record that the first statement just inserted, use this clause. Either that or you create an "update-able" view to handle it.

Happy Coding!!!

~~ CK

[Brian Wolfe]

I'm not quite sure what you mean by "do it all in sql server" - I am doing it all in php, using ADOdb as my go-between.

The statements are wrapped in a transaction - my first post makes that clear. I even gave the code of the transaction it will be wrapped in.

So, to make this a single action, is this what you mean?

DECLARE @dummytablevar(pri_ID int);
INSERT INTO table1(some_data)
    OUTPUT INSERTED.pri_ID
        INTO @dummytablevar
VALUES ('somethinghere');
INSERT table2(pri_ID,other_data)
VALUES (INSERTED.pri_ID,'somethingelse');

[ck9663]

What you gave is a block of command,not transaction. I am talking about this

Your last post tells me you're on the right track. The reason am recommending you wrap in transaction so that when there's an error on your second insert, you can rollback everything. Otherwise, you'll have data on your first table, but not on the second table. Also, identity property cannot be reused immediately. You need a couple of steps to reuse it and it has to be properly handled. Otherwise you'll have missing identity values and worse the tables being not sync.

Good Luck!!!

~~ CK

[Brian Wolfe]

Okay, I think I'm getting closer here. I'm using adoDB to talk to SQL server - here's what I have so far.

$sql = "INSERT INTO table1(some_data) VALUES ("somethinghere")";
$db->StartTrans();
$db->_Execute($sql);
$newID = $db->Insert_ID();
$sql2 = "INSERT INTO table2(table1_ID,other_data) VALUES ($newID,"somethingelse")
$db->_Execute($sql2);
$db->CompleteTrans();
 

Now, from what I understand, our older version of ADOdb (3.9x) doesn't handle Insert_ID the same way as @@IDENTIFY would (it doesn't work at all during a wrapped transaction), but I'm not sure quite how to work @@IDENTIFY or some variation into the script. Any ideas here?

[ck9663]

Your sql variable doesn't need to have only one statement. It can contain multiple statements. As long as you split it with semi colon inside the quote. Not that semicolon outside. So your two insert statement can be in one variable as long as it can accommodate the string.

What I am trying to recommend is to create a stored proc that you can pass those values, let your stored procedure handle the insert, the transaction processing and the @@IDENTITY. The problem with merely using @@IDENTITY is you have to consider if there are multiple session trying to update your table.

Good Luck!!!

~~ CK

[Brian Wolfe]

The semicolon outside is the endline for php.

I discovered a different method that works beautifully when I put it into the SQL editor directly, but it's not functional when executing through php.

INSERT INTO table1(some_data)
VALUES ('some notes');
INSERT INTO table2(Pri_ID,other_data)
VALUES (IDENT_CURRENT('table1'),'something else')
 

I'll head over to the php forums for the rest of this, then. I think my issues are stemming from ADOdb at this point. Thanks for all your help, ck!