Multiple inserts and selects

shmick25

Hi, I have two table with a one to many relationship between them.

Basically the first table, I need to insert a user_id and a date, and in the second table I need to insert the primary_id of the first table (of the record that was just created) and additional values.

What is the best way of doing this?

Basically, at the moment I have:

INSERT INTO payment_details (dt_created,user_id) VALUES (getdate(),508)
SELECT payment_id From payment_details ORDER BY DESC -- To get the last entered value
INSERT INTO user_custom_payment (payment_id,description,dt_from,dt_to,dt_created,active,amount)
VALUES()...

Is this the only way or is there a better method? Can you do it in the one sql statement?

Thanks,

tomhath

Nobody likes to be told it, but you just asked an RTFM question: 😉

http://www.phpbuilder.com/manual/function.mysql-insert-id.php

shmick25

Hi THere,

Eys, I know about the function for mysql, is there something we can use for sql server?

enterume

Just to share something here. Normally, in order to to allow our code workable in any pluform DB, I will get the max_id in the targeted table i want to insert record.

In your case, I will get the max_id from payment_details at first, then add the value by one(eg: $payment_id++). By now, can execute the insert method with the new $payment_id value.

Depend on the auto increment is not a idea way to do insert query, if you wish your code to be workable in other db like MSSQL and Oracle(it use some call 'sequence').

Lars_Berg

After insert in MSSQL it is possible to use the query

select @@identity

in order to get the value of the newly generated identity value.