Inserting into two tables

chocolate

I need to insert rows into two tables, the problem is that i need the generated id (a primary key) from the first table to store it onto the next table.

something like :

INSERT INTO aaaa (text) VALUES ('Some info');
INSERT INTO bbbb (aaaaid, sometext) VALUES (?????, 'Some text');

where aaaaid is the id of the row inserted in the first statement.

i know how to use nextval. but is it compatible with other RDBMS's? (i use pgsql). How safe is it when it comes to muti-user highvolume databases?

cgraz

I need to insert rows into two tables, the problem is that i need the generated id (a primary key) from the first table to store it onto the next table.

Have a look at mysql_insert_id()

Cgraz

chocolate

but i'm using postgreSQL (see post) and not my_sql. Also, the system may be ported later to another RDBMS.

cgraz

woah! i completely missed that.

I'm not familiar at all with postgre. If no one else offers a solution, I'd just run a select statement to select the highest id then use that in your second insert.

Cgraz

chocolate

hmm. is that safe? would you be able to get the number befor ethe next transaction is posted?

cgraz

yeah. You'd just put them in the right order. In Pseudo terms:

INSERT
SELECT last id INSERTED
INSERT using the id retrieved from last SELECT

Now as I mentioned, I'm not familiar with postgre at all, so there may be an easier solution. If you can't find anything else, this should work.

Cgraz