CODEWITHSUNDEEP
javascriptsqlnode.jssql-serverexpress
Sai Jeevan Balla
Sai Jeevan Balla

Reputation: 145

How to Insert bulk Array of data in mssql table using nodejs

I am using node-mssql, LInk : https://www.npmjs.com/package/mssql

i want to insert bulk array of data in mssql database

i am getting array of array records eg: [[row1],[row2],[row3]]

i want to insert these records in mssql database 

import * as sql from "mssql";

const conn = new sql.ConnectionPool({
   user: "XXXXXXXXX",
   password: "XXXXXXXXXXX",
   server: "XXXXXXXXXXX",
   database: "TESTDATA",
   options: {
    instanceName: "XXX"
   },
   pool: {
     max: 10,
     min: 0,
     idleTimeoutMillis: 30000
   }
 });

conn.connect()

var values = [[john,1,4,80],[jenny,2,4,78],[abhi,3,4.60],[ram,4,4,90]]

new sql.Request(conn)
    .query(`INSERT INTO CLASS_TABLE (NAME, ROLL, CLASS, MARKS) VALUES
 ${values.map(row=>{ var num = Array('(' + row.join() + ')' ).join(); return num })}`)

Error : The label '@' has already been declared. Label names must be unique within a query batch or stored procedure.

Upvotes: 0

Views: 8505

Answers (3)

Abdul Akibu
Abdul Akibu

Reputation: 1

Update to Williams answer:

import {
ConnectionPool,
Table,
VarChar,
Int,
} from 'mssql';
let values = [[john,1,4,80],[jenny,2,4,78],[abhi,3,4,60],[ram,4,4,90]];
const table = new Table('CLASS_TABLE');
table.create = false;
// Ignore the identity field
table.columns.add('NAME', VarChar, { nullable: true });
table.columns.add('ROLL', Int, { nullable: true });
table.columns.add('CLASS', Int, { nullable: true });
table.columns.add('MARKS', Int, { nullable: true });
//If you have an array of arrays you can map it without hard coding the amount of values needed
//for (let j = 0; j < values.length; j += 1) {
// table.rows.add(
//    values[j][0],
//    values[j][1],
//    values[j][2],
//    values[j][3]
//  );
//}

//Updated way allows for dynamically added columns coming from something like a config file
values.forEach(row => table.rows.add.apply(table.rows,row));
const request = pool.request();
const results = await request.bulk(table);
console.log(`rows affected ${results.rowsAffected}`);

Upvotes: 0

William Bell
William Bell

Reputation: 41

Better answer:

import {
  ConnectionPool,
  Table,
  VarChar,
  Int,
} from 'mssql';

var values = [[john,1,4,80],[jenny,2,4,78],[abhi,3,4,60],[ram,4,4,90]];

const table = new Table('CLASS_TABLE');
table.create = false;
// Ignore the identity field
table.columns.add('NAME', VarChar, { nullable: true });
table.columns.add('ROLL', Int, { nullable: true });
table.columns.add('CLASS', Int, { nullable: true });
table.columns.add('MARKS', Int, { nullable: true });

for (let j = 0; j < values.length; j += 1) {
  table.rows.add(
    values[j][0],
    values[j][1],
    values[j][2],
    values[j][3]
  );
}
const request = pool.request();

const results = await request.bulk(table);
console.log(`rows affected ${results.rowsAffected}`);

Upvotes: 1

James
James

Reputation: 82096

Use bulk

const table = new sql.Table('CLASS_TABLE');
table.columns.add('NAME', sql.NVarChar(15), {nullable: false});
table.columns.add('ROLL', sql.Int, {nullable: false});
table.columns.add('CLASS', sql.Int, {nullable: false});
table.columns.add('MARKS', sql.Int, {nullable: false});

values.forEach(arr => table.rows.add.apply(null, arr));

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

Upvotes: 2

Related Questions