Summary: in this tutorial, you will learn how to perform a BULK
INSERT
command in SQL Server from a Node.js application.
This tutorial begins where the Import data from a CSV file into SQL Server from Node.js tutorial left off.
We’ll perform a bulk insert customer data from a CSV file into the Customers
table of the BookStore
database:
- Use the
csv-parse
package to read customer data from the./data/customers.csv
file. - Use the
mssql
package to perform a bulk insert request.
Bulk insert customer data
Step 1. Create a new file bulk.js
in the project directory.
Step 2. Copy the CSV file (customers) that contains customer data to the data
directory:
FirstName,LastName,Email,PhoneNumber,Address
John,Doe,[email protected],123-456-7890,"123 Elm Street, Springfield, IL"
Jane,Smith,[email protected],234-567-8901,"456 Oak Avenue, Metropolis, NY"
Alice,Johnson,[email protected],345-678-9012,"789 Pine Road, Gotham, NJ"
Code language: SQL (Structured Query Language) (sql)
Step 3. Define a function that reads customer data from the CSV file using the csv-parse
package:
const readCustomerCSV = async (filename) => {
const customers = [];
return new Promise((resolve, reject) => {
createReadStream(filename)
.pipe(parse({ columns: true, trim: true }))
.on('data', (data) => customers.push(data))
.on('end', () => resolve(customers))
.on('error', (error) => reject(error));
});
};
Code language: SQL (Structured Query Language) (sql)
The readCustomerCSV
function returns an array of customer objects, where each object contains the FirstName
, LastName
, Email
, PhoneNumber
, and Address
properties.
Step 4. Define the insertCustomers
function that performs a bulk insert of customer data from the customers.csv
file into the Customers
table:
const insertCustomers = async (filename) => {
try {
// read customer data from a CSV file
const customers = await readCustomerCSV(filename);
// Connect to the SQL Server
await sql.connect(config);
// Initialize a Table object
const table = new sql.Table('Customers');
// Set to false because the table already exists.
// If the table does not exist, set to true , it will create the table
table.create = false;
table.columns.add('FirstName', sql.VarChar(100), { nullable: false });
table.columns.add('LastName', sql.VarChar(100), { nullable: false });
table.columns.add('Email', sql.VarChar(255), { nullable: false });
table.columns.add('PhoneNumber', sql.VarChar(20), { nullable: false });
table.columns.add('Address', sql.VarChar(255), { nullable: false });
// Add rows to the table
customers.forEach((c) => {
table.rows.add(
c.FirstName,
c.LastName,
c.Email,
c.PhoneNumber,
c.Address
);
});
// Create a new bulk request to the Customers table
const request = new sql.Request();
// Execute the bulk insert
request.bulk(table);
} catch (err) {
console.error(err);
}
};
Code language: SQL (Structured Query Language) (sql)
How it works.
First, read data from the CSV file by calling the readCustomerCSV
function:
const customers = await readCustomerCSV(filename);
Code language: SQL (Structured Query Language) (sql)
Second, connect to the SQL Server:
await sql.connect(config);
Code language: SQL (Structured Query Language) (sql)
Third, initialize a Table
object with the table name Customers
:
const table = new sql.Table('Customers');
Code language: SQL (Structured Query Language) (sql)
Since the Customers
table already exists, set the create
property to false
:
table.create = false;
Code language: SQL (Structured Query Language) (sql)
Note that if the table does not exist, and you set the create
property to true
, it will automatically create the Customers
table in SQL Server.
Fourth, define the columns for the Customers
table.
table.columns.add('FirstName', sql.VarChar(100), { nullable: false });
table.columns.add('LastName', sql.VarChar(100), { nullable: false });
table.columns.add('Email', sql.VarChar(255), { nullable: false });
table.columns.add('PhoneNumber', sql.VarChar(20), { nullable: false });
table.columns.add('Address', sql.VarChar(255), { nullable: false });
Code language: SQL (Structured Query Language) (sql)
Please note that this step is required even though the Customers
table already exists. Additionally, if the Customers
table does not exist, you need to define the primary key column (CustomerID
) as well.
Fifth, add rows to the Customers
table:
customers.forEach((c) => {
table.rows.add(c.FirstName, c.LastName, c.Email, c.PhoneNumber, c.Address);
});
Code language: SQL (Structured Query Language) (sql)
Sixth, create a bulk request to the Customers
table:
const request = new sql.Request();
request.bulk(table);
Code language: SQL (Structured Query Language) (sql)
Finally, display the error if it occurs in the catch
block:
console.error(err);
Code language: SQL (Structured Query Language) (sql)
Here’s the complete bulk.js
file:
import { createReadStream } from 'fs';
import { parse } from 'csv-parse';
import sql from 'mssql';
import { config } from './config.js';
const readCustomerCSV = async (filename) => {
const customers = [];
return new Promise((resolve, reject) => {
createReadStream(filename)
.pipe(parse({ columns: true, trim: true }))
.on('data', (data) => customers.push(data))
.on('end', () => resolve(customers))
.on('error', (error) => reject(error));
});
};
const insertCustomers = async (filename) => {
try {
// read customer data from a CSV file
const customers = await readCustomerCSV(filename);
// Connect to the SQL Server
await sql.connect(config);
// Initialize a Table object
const table = new sql.Table('Customers');
// Set to false because the table already exists.
// If the table does not exist, set to true , it will create the table
table.create = false;
table.columns.add('FirstName', sql.VarChar(100), { nullable: false });
table.columns.add('LastName', sql.VarChar(100), { nullable: false });
table.columns.add('Email', sql.VarChar(255), { nullable: false });
table.columns.add('PhoneNumber', sql.VarChar(20), { nullable: false });
table.columns.add('Address', sql.VarChar(255), { nullable: false });
// Add rows to the table
// Add rows to the table
customers.forEach((c) => {
table.rows.add(
c.FirstName,
c.LastName,
c.Email,
c.PhoneNumber,
c.Address
);
});
// Create a new bulk request to the Customers table
const request = new sql.Request();
// Execute the bulk insert
request.bulk(table);
} catch (err) {
console.error(err);
}
};
export { insertCustomers };
Code language: SQL (Structured Query Language) (sql)
Step 5. Modify the index.js
file to read data from the ./data/customer.csv
file and bulk insert it into the Customers table:
import { insertCustomers } from './bulk.js';
insertCustomers('./data/customers.csv');
Code language: SQL (Structured Query Language) (sql)
Step 6. Execute the following command to run the bulk.js
file:
npm start
Code language: SQL (Structured Query Language) (sql)
Verifying the bulk insert
Step 1. Launch SQL Server Management Studio and connect to the SQL Server.
Step 2. Execute the following command to retrieve data from the Customers
table:
SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)
Output:
Download the Project Source Code
Download the project source code
Summary
- Create a Table object and call the
bulk()
method to perform a bulk insert request to the SQL Server.