• 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:
  • Tim Cooke
  • Campbell Ritchie
  • paul wheaton
  • Ron McLeod
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:

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

 
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks ppl
Now Can somebody shed a light on stored procedures for MS SQL server??
Farooq
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic