Inserting multiple rows into SQL Server from Node.js

Question

I am working on a project that will upload some records to SQL Server from a node.js program. Right now, this is my approach (inside an async function):

con = await sql.connect(`mssql://${SQL.user}:${SQL.password}@${SQL.server}/${SQL.database}?encrypt=true`);
for (r of RECORDS) {
        columns = `([column1], [column2], [column3])`;
        values = `(@col1, @col2, @col3)`;
        await con
            .request()
            .input("col1", sql.Int, r.col1)
            .input("col2", sql.VarChar, r.col2)
            .input("col3", sql.VarChar, r.col3)
            .query(`INSERT INTO [dbo].[table1] ${columns} VALUES ${values}`);
}

Where records is an array of objects in the form:

RECORDS = [
    { col1: 1, col2: "asd", col3: "A" },
    { col1: 2, col2: "qwerty", col3: "B" },
    // ...
];

This code works, nevertheless, I have the feeling that it is not efficient at all. I have an upload of around 4k records and it takes roughly 10 minutes, it does not look good.

I believe if I can create a single query - instead of wrapping single inserts inside a for loop - with all the record values it will be faster, and I know there is a syntax for reaching that in SQL:

INSERT INTO table1 (column1, column2, column3) VALUES (1, "asd", "A"), (2, "qwerty", "B"), (...);

However I cannot find any documentation from mssql module for node on how to prepare the parameterized inputs to do everything in a single transaction.

Can anyone guide me into the right direction?

Thanks in advance.

Answer 1

As pointed out by @JoaquinAlvarez, bulk insert should be used as replied here: Bulk inserting with Node mssql package

For my case, the code was like:

return await sql.connect(`mssql://${SQL.user}:${SQL.password}@${SQL.server}/${SQL.database}?encrypt=true`).then(() => {
    table = new sql.Table("table1");
    table.create = true;
    table.columns.add("column1", sql.Int, { nullable: false });
    table.columns.add("column2", sql.VarChar, { length: Infinity, nullable: true });
    table.columns.add("column3", sql.VarChar(250), { nullable: true });

    // add here rows to insert into the table
    for (r of RECORDS) {
        table.rows.add(r.col1, r.col2, r.col3);
    }

    return new sql.Request().bulk(table);
});

The SQL data types have to match (obviously) the column type of the existing table table1. Note the case of column2, which is a column defined in SQL as varchar(max).

Thanks Joaquin! I went down on the time significantly from 10 minutes to a few seconds