com.microsoft.sqlserver.jdbc

Class SQLServerBulkCopy

java.lang.Object

com.microsoft.sqlserver.jdbc.SQLServerBulkCopy

All Implemented Interfaces:

AutoCloseable

public class SQLServerBulkCopy
extends Object
implements AutoCloseable

Lets you 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.

Constructor Summary

Constructors

Constructor and Description
SQLServerBulkCopy(Connection connection) Initializes a new instance of the SQLServerBulkCopy class using the specified open instance of SQLServerConnection.
SQLServerBulkCopy(String connectionUrl) Initializes and opens a new instance of SQLServerConnection based on the supplied connectionString.

Method Summary

Modifier and Type	Method and 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() Gets the current SQLServerBulkCopyOptions.
String	getDestinationTableName() Gets the name of the destination table on the server.
protected Object	getTemporalObjectFromCSVWithFormatter(String valueStrUntrimmed, int srcJdbcType, int srcColOrdinal, java.time.format.DateTimeFormatter dateTimeFormatter)
void	setBulkCopyOptions(SQLServerBulkCopyOptions copyOptions) Update the behavior of the SQLServerBulkCopy instance according to the options supplied.
void	setDestinationTableName(String tableName) Sets the name of the destination table on the server.
void	writeToServer(ISQLServerBulkRecord 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 Detail

SQLServerBulkCopy

public SQLServerBulkCopy(Connection connection)
                  throws SQLServerException

Initializes a new instance of the SQLServerBulkCopy class 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 SQLException

Initializes and opens a new instance of SQLServerConnection based on the supplied connectionString.

Parameters:

connectionUrl - Connection string for the destination server.

Throws:

SQLException - If a connection cannot be established.

Method Detail

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()

Gets 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()

Gets 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.

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(ISQLServerBulkRecord 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 - SQLServerBulkReader 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,
                                                       java.time.format.DateTimeFormatter dateTimeFormatter)
                                                throws SQLServerException

Throws:

SQLServerException