wood burning stoves 2.0*
The moose likes JDBC and the fly likes How Do I Insert records in multiple tables thru 1 SQL Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How Do I Insert records in multiple tables thru 1 SQL Query" Watch "How Do I Insert records in multiple tables thru 1 SQL Query" New topic
Author

How Do I Insert records in multiple tables thru 1 SQL Query

Farooq Ali
Ranch Hand

Joined: Sep 30, 2000
Posts: 43
Hello All
I want to insert a new record but the fields are spread on 3 different tables that is the entries in one table are linked to the other tables. Is there a way I can insert the data with one Insert query???
Farooq
john_guthrie
Greenhorn

Joined: Jun 14, 2001
Posts: 16
Short answer: no
The standard SQL insert statement allows for only one table. Having said that, make sure you use a transaction to insert into the three tables (with, alas, three insert statements).
Kashif Qayyum
Greenhorn

Joined: Aug 15, 2001
Posts: 1
Perhaps a better way to do this than have 3 update requests and a trans_start and trans_commit is to write a single stored procedure on the database, and execute it with a PreparedStatement.
This has a LOT of performance benefit. For one, you have less remote calls from the Java app to the DB. Secondly, the stored procedure is pre-compiled and can be quickly executed. Executing raw SQL from JDBC suffers from having to compile the query each time, only to throw it away.
If you're executing the same query with different parameters repetedly, you should definitely be using PreparedStatements.
The above is from my work with oracle. Not sure how it applies to other systems, though I suspect it should be the same.
- kashif Q.
Farooq Ali
Ranch Hand

Joined: Sep 30, 2000
Posts: 43
Thanks ppl
Now Can somebody shed a light on stored procedures for MS SQL server??
Farooq
 
 
subject: How Do I Insert records in multiple tables thru 1 SQL Query
 
Similar Threads
possible to insert data into two tables from one sql?
Hibernate Insert Error
batch insert to different tables for one transaction
SQL queries
Auto Increment