node-bcp
A node.js wrapper for the SQL Server bcp utility which allows for bulk insert and export operations.
Install
npm install bcp
You will need the bcp
utility installed on your system. If you're running on Windows and use SQL Server, you probably already have this. On Linux, you can use Microsoft's ODBC Driver for Linux which comes includes the bcp utility. On OSX, sorry, better luck next time.
This library does not support FreeTDS's reimplementation called
freebcp
. Their implementation is very incomplete and differs enough from the Microsoft version
Bulk Import Example
Imagine we have this table:
MyDatabase.dbo.MyTable
Column | Type |
---|---|
Id | Int Identity |
MyDate | DateTime |
MyFloat | Float |
MyString | NVarChar(50) |
var Bcp = ; // Initialize a Bcp object with settings like connection info, database, encoding, etc.var b = user: 'login_name' password: 'password' database: 'MyDatabase' fieldTerminator: '\t::\t' // make sure to pick a character sequence not found in your data rowTerminator: '\t::\n' unicode: true checkConstraints: true; // now, prepare a bulk insert. Notice how we excluded the Id column because it's an identity // and it would be ignored anyway, unless we set useIdentity: true. All columns in the table, // but not listed in the column list here will be inserted as NULL// The column names do not need to match the same casing as the SQL Server columns, but they // must match the properties of the objects passed to writeRows(). Column order is insignificant.b;
Bulk Export Example
var b = user: 'login_name' password: 'password' database: 'MyDatabase' checkConstraints: false unicode: false native: true // if need native data format.; var customOptions = read: false keepFiles: true sql: 'select * from MyDatabase.dbo.MyTable where id = 1' // if need select query, do like this.; b;
For a full description of all of the options which can be passed to the Bcp constructor, see lib/Bcp.js and Microsoft's bcp documentation.
Other Notes
This library is incomplete, and not very well tested. It needs lots more documentation, features, better error reporting, and love. Bulk Export is partially implemented, but there are no examples yet. Use at your own risk. May cause drowsiness and irritability.