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
const options = { keepNulls: true };

// instantiate - provide the table where you'll be inserting to, options and a callback
const 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 });

// execute
connection.execBulkLoad(bulkLoad, [
  { myInt: 7, myString: 'hello' },
  { myInt: 23, myString: 'world' }
]);

For more details on the ways the row data can be formatted, see connection.execBulkLoad.

options(optional)
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 row objects 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.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.