Node.js SQL Server Bulk Insert

[Savigo]

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone 

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 wherethe Import data from a CSV file into SQL Server from Node.js tutorialleft 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 startCode 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.