insert involving multiple tables

[bdina]

I am using PHP5 under linux against a MS SQL 2000 database. What I want to do is insert data that involves multiple tables, I have learned the hard way that using views will not accomplish this. The tough part about what I am doing is that the secondary table's insert relies on the ID created by inserting into the primary table. I suppose I could retrieve the ID of the newly created record, but I feel like this is sloppy and there must be a better way to do this. If there are no better ways to do this, then I suppose by obtaining the previously inserted records ID will work, however I am not sure the best way to do that. I am figuring I can run a select against the table and search for either something that should be specific (which sounds scary) or I can cycle through the records and pick the last one (which also sounds somewhat scary, but not as scary).

thanks!
--Bryan

 

[sleipnir214]

Cycling through the records and picking the "last" one is a very bad idea because you can't guaranty concurrency. If user B inserts a record in between the time that user A inserts a record and the time he begins looking for the "last" record, then user A will fetch user B's record.


Your question involves some specifics of SQL Server 2k internals. I recommend that you ask this question in of Tek-Tips' SQL Server fora.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[ericbrunson]

The proper (portable) way to do it would be to get the ID from a sequence first, insert it into the parent table, then use the ID for the child table.

Using auto_increment fields is non-portable, kinda lazy and ends up creating predicaments like you have right there.