java：如何使用java jdbc for Sql Server执行带有标识列的批插入

Question

我有一个csv文件，需要使用SQLServerBulkCopy将其写入Sql Server表。我正在使用SQLServerBulkCSVFileRecord从文件中加载数据。

create table TEST
(
    ID int identity,
    FIELD_1 int,
    FIELD_2 varchar(20)
)

4279895;AA00000002D
4279895;AA00000002D
4279895;AA00000002D
4279896;AA00000003E
4279896;AA00000003E
4279896;AA00000003E

com.microsoft.sqlserver.jdbc.SQLServerException: Source and destination schemas do not match.
    at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.validateColumnMappings(SQLServerBulkCopy.java:1749)
    at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:1579)
    at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:606)

try (
        Connection targetConnection = DriverManager.getConnection(Configuration.TARGET_CONNECTION_URL);
        SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(targetConnection);
        SQLServerBulkCSVFileRecord fileRecord = new SQLServerBulkCSVFileRecord(csvPath, Charsets.UTF_8.toString(), ";", false);
) {

    SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
    copyOptions.setKeepIdentity(false);

    bulkCopy.setBulkCopyOptions(copyOptions);

    fileRecord.addColumnMetadata(1, null, java.sql.Types.INTEGER, 0, 0); // FIELD_1 int    
    fileRecord.addColumnMetadata(2, null, java.sql.Types.VARCHAR, 20, 0); // FIELD_2 varchar(20)

    bulkCopy.setDestinationTableName("TEST");                
    bulkCopy.writeToServer(fileRecord);

}

catch (Exception e) {

    // [...]

}

如果从表中移除identity列，则大容量插入将成功结束。使用java jdbc for Sql Server执行标识大容量插入哪个是正确的？

Answer 1

我认为您不需要设置此选项copyoptions.setkeEpidEntity（false）；

移除此行后再试。您也可以参考这篇文章SqlBulkCopy Insert with Identity列