Subject Re: INSERT INTO Multiple Tables
Author Roman Rokytskyy

> Sorry for the naïve question, but is it possible to insert into
> multiple tables using a single statement?
> Say I want to do the following:
> PreparedStatement ps = con.prepareStatement(
> "INSERT INTO Persistent(id,classtype) VALUES (?,?); " +
> "INSERT INTO Named(persistentId,name) VALUES (?,?)" );
> ps.setInt( 1, id );
> ps.setString( 2, classtype );
> ps.setInt( 3, id );
> ps.setString( 4, name );
> Note that I want the parameters to be positioned 1,2,3, and 4.

Not with INSERT statement. However you can create stored procedure,
and use it:

CREATE PROCEDURE my_insert_procedure(id INTEGER, classtype VARCHAR
(20), persistentId INTEGER, name VARCHAR(20))
INSERT INTO Persistent(id, classtype) VALUES (:id, :classtype);
INSERT INTO Named(persistentId, name) VALUES
(:persistentId, :name);


and then you can use following:

CallableStatement cs =
connection.prepareCall("{call my_insert_procedure(?, ?, ?, ?)}");

cs.setInt(1, ...);

But why do you want to use one statement to insert into two tables?
The only reason I can imagine is having something similar to nested
transation where data are inserted either into two tables or into
none. If this is the case, stored procedure is what you need.

Best regards,
Roman Rokytskyy