Node.js SQL Server: Bulk Insert

Summary: in this tutorial, you will learn how to perform a BULKINSERT 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"

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));
  });
};

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);
  }
};

How it works.

First, read data from the CSV file by calling the readCustomerCSV function:

const customers = await readCustomerCSV(filename);

Second, connect to the SQL Server:

await sql.connect(config);

Third, initialize a Table object with the table name Customers:

const table = new sql.Table('Customers');

Since the Customers table already exists, set the create property to false:

table.create = false;

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 });

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);
});

Sixth, create a bulk request to the Customers table:

const request = new sql.Request();
request.bulk(table);

Finally, display the error if it occurs in the catch block:

console.error(err);

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 };

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');

Step 6. Execute the following command to run the bulk.js file:

npm start

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;

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.

Was this tutorial helpful?