Fork me on GitHub

BulkLoad

A BulkLoad instance is used to perform a bulk insert. Use connection.newBulkLoad to create a new instance, and connection.execBulkLoad to execute it (see usage section).

Usage

// optional BulkLoad options
var options = { keepNulls: true };

// instantiate - provide the table where you'll be inserting to, options and a callback
var bulkLoad = connection.newBulkLoad('MyTable', options, function (error, rowCount) {
  console.log('inserted %d rows', rowCount);
});

// setup your columns - always indicate whether the column is nullable
bulkLoad.addColumn('myInt', TYPES.Int, { nullable: false });
bulkLoad.addColumn('myString', TYPES.NVarChar, { length: 50, nullable: true });

// add rows
bulkLoad.addRow({ myInt: 7, myString: 'hello' });
bulkLoad.addRow({ myInt: 23, myString: 'world' });

// execute
connection.execBulkLoad(bulkLoad);
options
checkConstraints

Honors constraints during bulk load, using T-SQL CHECK_CONSTRAINTS. (default: false)

fireTriggers
Honors insert triggers during bulk load, using the T-SQL FIRE_TRIGGERS. (default: false)
keepNulls
Honors null value passed, ignores the default values set on table, using T-SQL KEEP_NULLS. (default: false)
tableLock
Places a bulk update(BU) lock on table while performing bulk load, using T-SQL TABLOCK. (default: false)

bulkLoad.addColumn(name, type, options)

Adds a column to the bulk load. The column definitions should match the table you are trying to insert into. Attempting to call addColumn after the first row has been added will throw an exception.

bulkLoad.addColumn('MyIntColumn', TYPES.Int, { nullable: false });
name

The name of the column.

type

One of the supported data types.

options

Additional column type information. At a minimum, nullable must be set to true or false.

  • nullable indicates whether the column accepts NULL values.
  • objName If the name of the column is different from the name of the property found on rowObj arguments passed to , then you can use this option to specify the property name.
  • length for VarChar, NVarChar, VarBinary. Use length as Infinity for VarChar(max), NVarChar(max) and VarBinary(max).
  • precision for Numeric, Decimal
  • scale for Numeric, Decimal, Time, DateTime2, DateTimeOffset

bulkLoad.addRow(row)

Adds a row to the bulk insert. This method accepts arguments in three different formats:

bulkLoad.addRow( rowObj )
bulkLoad.addRow( columnArray )
bulkLoad.addRow( col0, col1, ... colN )
rowObj

An object of key/value pairs representing column name (or objName) and value.

columnArray

An array representing the values of each column in the same order which they were added to the bulkLoad object.

col0, col1, ... colN

If there are at least two columns, values can be passed as multiple arguments instead of an array. They must be in the same order the columns were added in.

bulkLoad.getRowStream()

Switches the BulkLoad object into streaming mode and returns a writable stream that can be used to send a large amount of rows to the server.

const bulkLoad = connection.newBulkLoad(...);
bulkLoad.addColumn(...);
const rowStream = bulkLoad.getRowStream();
connection.execBulkLoad(bulkLoad);
rowSource.pipe(rowStream);

In streaming mode, bulkLoad.addRow() cannot be used. Instead all data rows must be written to the returned stream object.

The stream implementation uses data flow control to prevent memory overload. stream.write() returns false to indicate that data transfer should be paused. After that, the stream emits a 'drain' event when it is ready to resume data transfer.

bulkLoad.getTableCreationSql()

This is simply a helper utility function which returns a CREATE TABLE SQL statement based on the columns added to the bulkLoad object. This may be particularly handy when you want to insert into a temporary table (a table which starts with #).

var sql = bulkLoad.getTableCreationSql();

A side note on bulk inserting into temporary tables: if you want to access a local temporary table after executing the bulk load, you'll need to use the same connection and execute your requests using connection.execSqlBatch instead of .execSql.

bulkLoad.setTimeout(timeout)

Sets a timeout for this bulk load.

bulkLoad.setTimeout(timeout);
timeout

The number of milliseconds before the bulk load is considered failed, or 0 for no timeout.

When no timeout is set for the bulk load, the options.requestTimeout of the Connection is used.