wood burning stoves 2.0*
The moose likes JDBC and the fly likes Creating multiple tables at once Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Creating multiple tables at once" Watch "Creating multiple tables at once" New topic
Author

Creating multiple tables at once

Matthew Tilic
Ranch Hand

Joined: Apr 07, 2011
Posts: 41

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
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
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).
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3436
    
  47

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

Joined: Feb 22, 2011
Posts: 75
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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3436
    
  47

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

Joined: Feb 22, 2011
Posts: 75
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

Joined: Apr 07, 2011
Posts: 41

Hi,

Thanks for the replies. I should have said I am using MySQL and will the multi insert sort out my foreign key issue?!
Philip Grove
Ranch Hand

Joined: Aug 18, 2009
Posts: 68

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.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Creating multiple tables at once
     
    Similar Threads
    db desgin question: how to improve the relationship in this schema
    Possible to find relationship
    SQL Query for two tables joining
    SQL Query for two tables joining
    complex table column type