Execute multiple sql statements with no errors when the insert fails

Virgil Yu 96 Reputation points
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class jdbcTest {
    public static void main(String[] arg) throws ClassNotFoundException, SQLException {

        String connectionUrl = "#############";
        Connection con = DriverManager.getConnection(connectionUrl, "###", "###");

        Statement stmt = con.createStatement();
        String sql1 = "INSERT INTO [tuj_dev].[dbo].[TLT_ORG2]\n" +
                "VALUES ('', '', '')\n" +
                "UPDATE [tuj_dev].[dbo].[TLT_ORG2]\n" +
                "SET [LVL_2_NM] = [TLT_NTID]";
        String sql2 = "UPDATE [tuj_dev].[dbo].[TLT_ORG2]\n" +
                "SET [LVL_2_NM] = [TLT_NTID]\n" +
                "INSERt INTO [tuj_dev].[dbo].[TLT_ORG2]\n" +
                "VALUES ('', '', '')";

When I try to execute the SQL statements(sql1, sql2), I got a different result.
For sql1, I got an exception "Violation of PRIMARY KEY constraint" and the update statement executes successfully.
For sql2, I got no errors and the update statement executes successfully as well.
Here I have a question, why the sql2 won't throw an exception or if I miss some settings?

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

2 additional answers

Sort by: Most helpful
  1. CarrinWu-MSFT 6,866 Reputation points

    Hi @Virgil Yu ,

    SQL server general tag is talking about the issues about SQL server. Your issue is not related to SQL server, suggest you add the correct tag for your issue and also post your issue in Java form. You can find all products that supported on Q&A forum from this link.

    Best regards,

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

  2. Erland Sommarskog 106.5K Reputation points

    I don't work with Java, so I cannot give the exact details, but the issue have been seen in other environments as well.

    One remedy is to add this command to the SQL batch: SET NOCOUNT ON. This suppresses the "rows affected" messages from SQL Server.

    The better solution is to iterate over all result sets returned from SQL Server. The first result set is the row count from SQL Server, but you need to move further to have the exception raised.

    The reason the latter is better is that there are situations where SET NOCOUNT ON is not going to help.