Insert in to multiple tables SQL

[Tony2005]

I'm pretty sure it can be done one way or another. Here is what I've got set up:

tblPostcode
---PostcodeID(PK)
---Postcode

tblTown
---TownID(PK)
---Town

tblStreet
---PostcodeID(FK)
---TownID(FK)
---More fields etc


I was thinking it would work something like this:
Insert the data in to tblPostcode and tblTown, retrieve their IDs or some how retain them when I insert, then with the ID's known, insert the relevant data in to tblStreet

If this could work how would I retrieve the ID's?

Cheers

Tony

[causticVapor]

retrieve the IDs beforehand and store them in a temporary array? this can be broken down into more than one step.

[ScaryBinary]

After you insert a new record into a table, you can usually retrieve it's Auto-Increment ID using some other function or query. For example, if you are using MySQL, you can run the query "SELECT LAST_INSERT_ID()". See the MySQL documentation for more examples: MySQL 5.0 Last Insert ID

If you're not using MySQL....then tell us what you are using and maybe we can find an easy way to get what you want. I'm pretty sure MS Access has a way to do this to, but I don't recall off the top of my head. ...And I've never dealt with SQL Server...

Other possible approaches include figuring out the next ID before hand, kind of like causticVapor noted, or somehow "manualy" querying the database for the most recent entry (maybe they're sorted by date, or ID or something) and then incrementing it for your next insert....?