Module com.microsoft.sqlserver.jdbc
Package com.microsoft.sqlserver.jdbc

Class SQLServerBulkCopy

java.lang.Object
com.microsoft.sqlserver.jdbc.SQLServerBulkCopy
All Implemented Interfaces:
Serializable, AutoCloseable
public class SQLServerBulkCopy extends Object implements AutoCloseable, Serializable
Provides functionality to efficiently bulk load a SQL Server table with data from another source.

Microsoft SQL Server includes a popular command-prompt utility named bcp for moving data from one table to another, whether on a single server or between servers. The SQLServerBulkCopy class lets you write code solutions in Java that provide similar functionality. There are other ways to load data into a SQL Server table (INSERT statements, for example), but SQLServerBulkCopy offers a significant performance advantage over them.
The SQLServerBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be read with a ResultSet or ISQLServerBulkRecord instance.
See Also:
Serialized Form

  • Constructor Summary

    Constructors
    Constructor
    Description
    SQLServerBulkCopy​(String connectionUrl)
    Constructs a SQLServerBulkCopy based on the supplied connectionString.
    SQLServerBulkCopy​(Connection connection)
    Constructs a SQLServerBulkCopy using the specified open instance of SQLServerConnection.

  • Method Summary

    Modifier and Type
    Method
    Description
    void
    addColumnMapping​(int sourceColumn, int destinationColumn)
    Adds a new column mapping, using ordinals to specify both the source and destination columns.
    void
    addColumnMapping​(int sourceColumn, String destinationColumn)
    Adds a new column mapping, using an ordinal for the source column and a string for the destination column.
    void
    addColumnMapping​(String sourceColumn, int destinationColumn)
    Adds a new column mapping, using a column name to describe the source column and an ordinal to specify the destination column.
    void
    addColumnMapping​(String sourceColumn, String destinationColumn)
    Adds a new column mapping, using column names to specify both source and destination columns.
    void
    clearColumnMappings()
    Clears the contents of the column mappings
    void
    close()
    Closes the SQLServerBulkCopy instance
    SQLServerBulkCopyOptions
    getBulkCopyOptions()
    Returns the current SQLServerBulkCopyOptions.
    String
    getDestinationTableName()
    Returns the name of the destination table on the server.
    protected Object
    getTemporalObjectFromCSVWithFormatter​(String valueStrUntrimmed, int srcJdbcType, int srcColOrdinal, DateTimeFormatter dateTimeFormatter)
    Returns the temporal object from CSV This method is called against jdbc41, but it require jdbc42 to work therefore, we will throw exception.
    void
    setBulkCopyOptions​(SQLServerBulkCopyOptions copyOptions)
    Update the behavior of the SQLServerBulkCopy instance according to the options supplied, if supplied SQLServerBulkCopyOption is not null.
    void
    setDestinationTableName​(String tableName)
    Sets the name of the destination table on the server.
    void
    writeToServer​(ISQLServerBulkData sourceData)
    Copies all rows from the supplied ISQLServerBulkRecord to a destination table specified by the destinationTableName property of the SQLServerBulkCopy object.
    void
    writeToServer​(ResultSet sourceData)
    Copies all rows in the supplied ResultSet to a destination table specified by the destinationTableName property of the SQLServerBulkCopy object.
    void
    writeToServer​(RowSet sourceData)
    Copies all rows in the supplied RowSet to a destination table specified by the destinationTableName property of the SQLServerBulkCopy object.

    Methods inherited from class java.lang.Object

    clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

  • Constructor Details

    • SQLServerBulkCopy

      public SQLServerBulkCopy​(Connection connection) throws SQLServerException
      Constructs a SQLServerBulkCopy using the specified open instance of SQLServerConnection.
      Parameters:
      connection - Open instance of Connection to destination server. Must be from the Microsoft JDBC driver for SQL Server.
      Throws:
      SQLServerException - If the supplied type is not a connection from the Microsoft JDBC driver for SQL Server.

    • SQLServerBulkCopy

      public SQLServerBulkCopy​(String connectionUrl) throws SQLServerException
      Constructs a SQLServerBulkCopy based on the supplied connectionString.
      Parameters:
      connectionUrl - Connection string for the destination server.
      Throws:
      SQLServerException - If a connection cannot be established.

  • Method Details

    • addColumnMapping

      public void addColumnMapping​(int sourceColumn, int destinationColumn) throws SQLServerException
      Adds a new column mapping, using ordinals to specify both the source and destination columns.
      Parameters:
      sourceColumn - Source column ordinal.
      destinationColumn - Destination column ordinal.
      Throws:
      SQLServerException - If the column mapping is invalid

    • addColumnMapping

      public void addColumnMapping​(int sourceColumn, String destinationColumn) throws SQLServerException
      Adds a new column mapping, using an ordinal for the source column and a string for the destination column.
      Parameters:
      sourceColumn - Source column ordinal.
      destinationColumn - Destination column name.
      Throws:
      SQLServerException - If the column mapping is invalid

    • addColumnMapping

      public void addColumnMapping​(String sourceColumn, int destinationColumn) throws SQLServerException
      Adds a new column mapping, using a column name to describe the source column and an ordinal to specify the destination column.
      Parameters:
      sourceColumn - Source column name.
      destinationColumn - Destination column ordinal.
      Throws:
      SQLServerException - If the column mapping is invalid

    • addColumnMapping

      public void addColumnMapping​(String sourceColumn, String destinationColumn) throws SQLServerException
      Adds a new column mapping, using column names to specify both source and destination columns.
      Parameters:
      sourceColumn - Source column name.
      destinationColumn - Destination column name.
      Throws:
      SQLServerException - If the column mapping is invalid

    • clearColumnMappings

      public void clearColumnMappings()
      Clears the contents of the column mappings

    • close

      public void close()
      Closes the SQLServerBulkCopy instance
      Specified by:
      close in interface AutoCloseable

    • getDestinationTableName

      public String getDestinationTableName()
      Returns the name of the destination table on the server.
      Returns:
      Destination table name.

    • setDestinationTableName

      public void setDestinationTableName​(String tableName) throws SQLServerException
      Sets the name of the destination table on the server.
      Parameters:
      tableName - Destination table name.
      Throws:
      SQLServerException - If the table name is null

    • getBulkCopyOptions

      public SQLServerBulkCopyOptions getBulkCopyOptions()
      Returns the current SQLServerBulkCopyOptions.
      Returns:
      Current SQLServerBulkCopyOptions settings.

    • setBulkCopyOptions

      public void setBulkCopyOptions​(SQLServerBulkCopyOptions copyOptions) throws SQLServerException
      Update the behavior of the SQLServerBulkCopy instance according to the options supplied, if supplied SQLServerBulkCopyOption is not null.
      Parameters:
      copyOptions - Settings to change how the WriteToServer methods behave.
      Throws:
      SQLServerException - If the SQLServerBulkCopyOption class was constructed using an existing Connection and the UseInternalTransaction option is specified.

    • writeToServer

      public void writeToServer​(ResultSet sourceData) throws SQLServerException
      Copies all rows in the supplied ResultSet to a destination table specified by the destinationTableName property of the SQLServerBulkCopy object.
      Parameters:
      sourceData - ResultSet to read data rows from.
      Throws:
      SQLServerException - If there are any issues encountered when performing the bulk copy operation

    • writeToServer

      public void writeToServer​(RowSet sourceData) throws SQLServerException
      Copies all rows in the supplied RowSet to a destination table specified by the destinationTableName property of the SQLServerBulkCopy object.
      Parameters:
      sourceData - RowSet to read data rows from.
      Throws:
      SQLServerException - If there are any issues encountered when performing the bulk copy operation

    • writeToServer

      public void writeToServer​(ISQLServerBulkData sourceData) throws SQLServerException
      Copies all rows from the supplied ISQLServerBulkRecord to a destination table specified by the destinationTableName property of the SQLServerBulkCopy object.
      Parameters:
      sourceData - ISQLServerBulkData to read data rows from.
      Throws:
      SQLServerException - If there are any issues encountered when performing the bulk copy operation

    • getTemporalObjectFromCSVWithFormatter

      protected Object getTemporalObjectFromCSVWithFormatter​(String valueStrUntrimmed, int srcJdbcType, int srcColOrdinal, DateTimeFormatter dateTimeFormatter) throws SQLServerException
      Returns the temporal object from CSV This method is called against jdbc41, but it require jdbc42 to work therefore, we will throw exception.
      Parameters:
      valueStrUntrimmed - valueStrUntrimmed
      srcJdbcType - srcJdbcType
      srcColOrdinal - srcColOrdinal
      dateTimeFormatter - dateTimeFormatter
      Returns:
      temporal object
      Throws:
      SQLServerException - if parsing error