-
Senior Member
Insert in to multiple tables SQL
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
"I once prayed to god for a bike, but quickly found out he didnt work that way...so I stole a bike and prayed for his forgiveness"
-
retrieve the IDs beforehand and store them in a temporary array? this can be broken down into more than one step.
-
Senior Member
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....?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|