Driver version

9.2.1.jre8

SQL Server version

Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) Jan 25 2021 20:16:12 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 20.04.2 LTS)

Client Operating System

Ubuntu 20.4

JAVA/JVM version

1.8.0_242-zulu-8.44.0.11

Table schema

Create Table tableDoesntMatter (col1 varchar(255));

Problem description

Complete code sample here: https://docs.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver15

Adding useBulkCopyForBatchInsert=true to the connection string enables SQL Server to use bulk insert instead of regular insert command. To enable this feature, "Insert Into" must be the only instruction of the statement. However this rule is sensitive to spaces in the command text.

This works:
Insert Into MyTable (col1) Values (?)

This doesn't work:
Insert Into MyTable(col1) Values (?)
(Note the missing space between the table name and the column list.)

In that latter case, the insert still succeeds, but the bulk insert mode is not triggered, resulting in a performance degradation.

Context: It's not easy to detect the bulk insert is disabled. It requires turning on SQL Server Profiler The command succeeds but is much slower. Moreover, such command texts could also be written by a library generating SQL statements.

I have not tried with spaces missing at other places.

Reproduction code

Connection connection = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=mydb;useBulkCopyForBatchInsert=true;"
PreparedStatement stmt = connection.prepareStatement("Insert Into MyTable(col1) Values (?)");

for (int i = 0; i < 100; i++) {
    stmt.setString(1, "test");
    stmt.addBatch();
}

stmt.executeBatch();

connection.close();