CODEWITHSUNDEEP
javascriptnode.jssql-servernode-mssql
ggnoredo
ggnoredo

Reputation: 821

Node MSSQL can't insert array

I want to insert many rows at once with an array data.

My array looks like this

[ [ '1234' ],
  [ '5678' ],
  [ '9123' ]... ]

and my query code

const sql = require('mssql');

const config = {
    user: 'sa',
    password: 'pass',
    server: 'ip',
    database: 'db'
};

async function get_numbers() {
    try {
        let pool = await new sql.ConnectionPool(config).connect();
        var qstring = `INSERT INTO numbers (gen_number) VALUES ?`;
        pool.request().query(qstring, mins, function (err, result) {
            if (err) throw err;
        });
        //pool.close();
    } catch (err) {
        console.log(err);
    }
};

But this gives incorrect syntax near ? error.

Upvotes: 0

Views: 3502

Answers (2)

James
James

Reputation: 82096

The mssql library has a bulk method for exactly this purpose.

const table = new sql.Table('numbers');
table.create = false; // presuming table already exists
table.columns.add('gen_number', sql.Int, { nullable: false });
// Add rows
numArr.forEach(x => table.rows.add(x));

const request = new sql.Request();
request.bulk(table, (err, result) => {
  // ... error checks
})

Upvotes: 3

Muhammad Usman
Muhammad Usman

Reputation: 10148

I assume that you want to insert the array contents 1234,5678 .. into the db. You can use

the following query 

var array = [ [ '1234' ], [ '5678' ],  [ '9123' ]... ];

var query = `var query = `INSERT INTO numbers (gen_number) VALUES ${array.join().split(",").map(i => '(' + i + ')').join()}``

This is simply joining the array contents and giving you a string that matches the SQL syntax to insert multiple values in one statement.

The above query will result into something like

"INSERT INTO numbers (gen_number) VALUES 1234,5678,9123"

Upvotes: 1

Related Questions