JDBC sql server: set IDENTITY_INSERT ON: no effect

Question

JDBC sql server: set IDENTITY_INSERT ON: no effect.

PreparedStatement stmt = connection.prepareStatement("SET IDENTITY_INSERT Table_FOO ON");
stmt.execute();
stmt.close();


PreparedStatement stmt2 = connection.prepareStatement("insert into Table_FOO (id, name) values (100, 'abc')");
stmt2.execute();
stmt2.close();

Error: still complain that DENTITY_INSERT is OFF.

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert explicit value for identity column in table 'Table_FOO' when IDENTITY_INSERT is set to OFF.

Answer 1

You could try combining the statements into 1 string and terminate statements with semicolon(s)

PreparedStatement stmt = connection.prepareStatement("SET IDENTITY_INSERT Table_FOO ON; " +
                                                     "insert into Table_FOO (id, name) values (100, 'abc');" +
                                                     "SET IDENTITY_INSERT Table_FOO OFF;");
stmt.execute();
stmt.close();