Using two INSERT statements for two different tables in a single SQL statement-CodePudding

I have two tables one is users and other is students. I wish to execute a single SQL statement that inserts into both these tables different values. The id is PK for users and I have referenced the same id in the student table with (id INTEGER NOT NULL PRIMARY KEY REFERENCES users(id))

I tried this:

INSERT INTO users (id, password, firstName, lastName, emailAddress, enrollDate, lastAccess, enabled, type) VALUES (100222222, 'password', 'Robert', 'McReady', '[email protected]', '2016-03-07', '2015-09-03', true, 's') 
AND 
INSERT INTO students (id, programCode, programDescription, year) VALUES (100222222, 'a', 'b', 3)

This statement throws syntax error (syntax error at or near "AND"), and I don't understand why. I am connecting this SQL prepared statement through Java so my code is like this:

String sqlInsert = "INSERT INTO users (id, password, firstName, lastName, emailAddress, enrollDate, lastAccess"   ", enabled, type) VALUES (100222222, 'password', 'Robert', 'McReady', '[email protected]', "   "'2016-03-07', '2015-09-03', 's', true)";

Is there a way so I can code this sqlInsert statement to insert into two different tables with different values in a single sql statement in Java?

CodePudding user response：

It is not possible to combine two insert statements with an AND. They need to be seperated.

In your code, depending on the language/framework, you can make one transaction for both statements, so they will be comitted at the same time to your DB.

CodePudding user response：

If the goal is to avoid repeating the value for the ID column, this could be done using a data modifying CTE:

with data (id, password, firstname, lastname, emailaddress, enrolldate, lastaccess, enabled, type) as (
  VALUES (100222222, 'password', 'Robert', 'McReady', '[email protected]', '2016-03-07', '2015-09-03', true, 's') 
), new_user as (
  INSERT INTO users (id, password, firstname, lastname, emailaddress, enrolldate, lastaccess, enabled, type) 
  select id, password, firstname, lastname, emailaddress, enrolldate, lastaccess, enabled, type
  from data
)
INSERT INTO students (id, programcode, programdescription, year) 
select id, 'a', 'b', 3
from data;

Postgres also allows to run two statements with a single Statement.executeUpdate() call as long as they are delimited with a ; (not with an "AND")

String sqlInsert = "insert into users (...) values (...);"  
                   "insert into students (...) values (...);";

However the above is not the correct approach. You should not put the actual values directly into your SQL strings.

It's better (safer and more efficient) to use a PreparedStatement. As the ID will be stored in a Java variable, there is no need to repeat it.

This has the additional advantage that you can pass the values for the DATE columns as proper LocalDate instances rather than strings. If you insert both in a single transaction you make sure that either both rows or nothing is inserted.

Something like the following (without proper error handling and cleanup!)

String usersInsert = "INSERT INTO users 
                    (id, password, firstname, lastname, emailaddress, enrolldate, lastaccess, enabled, type) 
                    VALUES (?,?,?,?,?,?,?,?,?)";
String studentInsert = "INSERT INTO INSERT INTO students (id, programcode, programdescription, year)  values (?,?,?,?)"; 


// Start a transaction
connection.setAutocommit(false);

int id = 100222222;
PreparedStatement stmtUsers = connection.prepareStatement(usersInsert);
stmtUsers.setInt(1, id);
stmtUsers.setString(2, "password");
stmtUsers.setString(3, "Robert");
...
stmtUsers.setObject(6, LocalDate.of(2016,3,7), Types.DATE);
stmtUsers.setObject(7, LocalDate.of(2015,9,3), Types.DATE);
...
stmtUsers.executeUpdate();

PreparedStatement stmtStudent = connection.prepareStatement(studentInsert);
stmtStudent.setInt(1, id);
stmtStudent.setString(2, "a");
...

stmtStudent.executeUpdate();

// end the transaction
connection.commit();

stmtStudent.close();
stmtUsers.close();