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
– Pointy, Commented Aug 9, 2020 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
– Salim Shamim, Commented Aug 9, 2020 at 14:58
@MartínJF I think I used backticks (````)and replaced value in query — Salim Shamim
– Salim Shamim, Commented Dec 19, 2021 at 10:07

Antonin Riche · Accepted Answer · 2020-08-09 14:02:41Z

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 9, 2020 at 14:02

Antonin Riche

5986 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pointy Over a year ago

SQL Server also supports prepared statement parameters. Building SQL out of strings is not good advice.

Antonin Riche Over a year ago

Indeed, but as I saw, it seems to be a different way than with mysql (with ?)

Salim Shamim Over a year ago

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 ?

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related