Insert multiple records into SQL Server (mssql) in node.js

Question

I am migrating a piece of code from mysql to mssql package of nodejs, in which its required to insert multiple rows.

Here's the sample code I am using for testing:

const db = require('mssql');

let config = {
    user: 'salim',
    password: 'admin',
    server: 'LAPTOP-JK45R', // You can use 'localhost\\instance' to connect to named instance
    database: 'master',
}

var pool;

//initialize connection pool
var connectPool = initializeConnectionPool();

function initializeConnectionPool() {
    pool = new db.ConnectionPool(config);
    pool.on('error', (err) => {
        logger.error(err);
    });

    return pool.connect();;
}

connectPool.then(async () => {
    let connection = await pool.request();
    console.log('Got pool connection...');
    var q = "INSERT INTO Person (name, address) VALUES ?";
    
    var values = [
        ['John', 'Highway 71'],
        ['Peter', 'Lowstreet 4'],
        ['Amy', 'Apple st 652'],
        ['Hannah', 'Mountain 21']
    ];

    let result = await connection.query(q,[values]);

    console.log(`Result: ${JSON.stringify(result)}`);
});

Its giving me error:

RequestError: Incorrect syntax near '?'.

I couldn't find any thing on official npm page of mssql, so I have been trying this: Insert multiple columns and rows into SQL Server with node js

In my code I am just using pool.

I also couldn't find how to log queries using this package, so couldn't figure out what the query is being formed.

It would be great to know any of the solution.

Perhaps there's something in the Node SQL Server interface that differs from my experience, but your `VALUES` clause should be a list of parenthesized lists like `(?, ?)`, one for each row. — Pointy, Aug 09 '20 at 14:16
There would be x number of rows and `(?,?) ` would be possible if I make string adding to it in each iteration. — Salim Shamim, Aug 09 '20 at 14:58
@MartínJF I think I used backticks (````)and replaced value in query — Salim Shamim, Dec 19 '21 at 10:07

score 1 · Answer 1 · answered Aug 09 '20 at 14:02

1

The ? is a way to pass parameter in mysql

In mssql it seem to be like ${entries}

refer to How to pass parameter to mssql query in node js

answered Aug 09 '20 at 14:02

Antonin Riche

548
6
10

SQL Server also supports prepared statement parameters. Building SQL out of strings is not good advice. – Pointy Aug 09 '20 at 14:04
Indeed, but as I saw, it seems to be a different way than with mysql (with `?`) – Antonin Riche Aug 09 '20 at 14:08
There could be n number of rows and the idea of building strings using those values doesn't seems a thing one would do in production, I want to know how is it possible as it is in `mysql` ? – Salim Shamim Aug 09 '20 at 14:56

Insert multiple records into SQL Server (mssql) in node.js

1 Answers1