• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Creating multiple tables at once

 
Ranch Hand
Posts: 41
MySQL Database Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Thanks in advance for any help.

I have two tables (Owner and Address). Currently I create the address and get the address ID then create the owner table and give it the address id. Then obtain the owner ID and then update the address table to include the owner id. The reason is that I can then search the owner or the address to find a link between the two. Thier is a FK from the owner to the Address.

I wanted to know if their is a way to save myself lots of extra coding and adding these two at the same time which will then configure themselves in the way described above.

Thanks
 
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't think you can insert into two tables in a single SQL statement. Atleast the database(Oracle) I have worked on has no option to do so.
Also if I understand correctly, you may need to rethink your data model. Why do you want to hold the address id in the owner table? Why the below flow wouldn't work for you?
Create the owner.
Create the address and update the owner_id. This way, you can have multiple addresses for the same owner. You will probably identify what is the primary address by marking one of the address as primary (a column to indicate primary flag).
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Sudheer Bhat wrote:I don't think you can insert into two tables in a single SQL statement. Atleast the database(Oracle) I have worked on has no option to do so.


Oracle can insert into several tables at once in fact, at least from 9i on. Search for Oracle multitable insert - or better look up Oracle's documentation of the INSERT statement. It's all there.

Your comments on the design is probably right. If the database is actually Oracle, sequences could be used to easily populate the tables, as I've already described here. Assuming that the design is corrected first, that is.


 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Martin for correcting me on the INSERT ALL. I missed it!.
Just curious, have you ever traced a INSERT ALL? Assuming insert all inserts into 2 tables a record each, the execute count shows 2 in the tkprof?
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Sudheer Bhat wrote:Thanks Martin for correcting me on the INSERT ALL. I missed it!.
Just curious, have you ever traced a INSERT ALL? Assuming insert all inserts into 2 tables a record each, the execute count shows 2 in the tkprof?


I didn't ever use it - just know it's here.

Execute in tkprof will be 1, assuming you executed the command just once. Every inserted row would probably be counted in row counts (though I'm just guessing here). You can easily verify this with tkprof in any case, if you're interested
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't have Oracle on my laptop. I will check it at work on Monday. Will trace and tkprof it on Monday! =).
 
Matthew Tilic
Ranch Hand
Posts: 41
MySQL Database Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Thanks for the replies. I should have said I am using MySQL and will the multi insert sort out my foreign key issue?!
 
Ranch Hand
Posts: 68
Netbeans IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
MySQL does not support inserting in multiple tables with a single statement, the INSERT ALL statement is specific to Oracle databases.

To me it sounds like you have three statements:
  • INSERT INTO Address
  • INSERT INTO Owner
  • UPDATE Address


  • Three statement on two tables from one action sounds messy, my first thought though is that you should verify your design because you do not seem to have a single location to store data. Of course such a design could be needed in certain places but you should explore other options before you implement it.
     
    Are you here to take over the surface world? Because this tiny ad will stop you!
    Smokeless wood heat with a rocket mass heater
    https://woodheat.net
    reply
      Bookmark Topic Watch Topic
    • New Topic