Unanswered: inserting new row, need to grab key from another table thats just inserted
i have 2 tables right now, po and notes...
the notes table contain the note_id and notetext, so example of a row would be:
1 this is the note for the first row
2 this is the note for computer parts
the po table has item info, description, po_id, and note_id, heres an example:
po_id item desc note_id
1 215-33 computer parts 2
the problem is, is there a way where say i insert the note row into the notes table first and then get the note_id of that, so that i can then insert the row into the po table which includes the note_id from the notes table?
would i have to use two separate inserts or can i use one? i was going to just insert the notes row first, and then use a query using the LIKE() function on the note text to get the note_id, but some notes may be the same as others, so the note_id wouldn't be the correct one and i think just getting the last row of the notes table would be bad too incase someone happens to insert a row into that table at the same time
If you are using some algorithm to generate your PK for the note table, just put it into a variable, and use that to generate the column you are using for your FK in the po table. If you are using an identity column in the notes table as your PK, then place into the variable the results of scope_identity(). Either way, you have the data necessary to generate the FK information needed for the po table from the primary key inserted in the note table.
It has to be two separate inserts, one for each table. Stay away from like or select max, or anything like that, because that will lead to concurrency problems later.