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

Question

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.

James · Accepted Answer · 2019-06-21 09:29:37Z

2

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
});

edited Jun 21, 2019 at 9:29

answered Jun 21, 2019 at 8:17

James

82.4k19 gold badges172 silver badges245 bronze badges

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

5 Comments

Sai Jeevan Balla Over a year ago

ERROR: Cannot read property 'push' of null", I am getting this Error if array is this [[john,1,4,80],[jenny,2,null,78],[abhi,3,4.60],[ram,4,4,null]]

James Over a year ago

@SaiJeevanBalla the example you gave all had values, if some columns can be null then you can just adjust {nullable: false} to {nullable: true} for those ones.

Sai Jeevan Balla Over a year ago

can you help me out of this ERROR: RequestError: Connection lost - read ECONNRESET, while inserting bulk data in mssql table it will lost the connection

Ya. Over a year ago

What if you want to insert into an existing table and don't want to specify the column type for each column? I.e. when you are devloping a generic framework, with table names and columns not known upfront?

James Over a year ago

@Ya. for this particular library, you must to define the column type. And, to be honest, I don't think you'll find many frameworks that won't at least require the consumer to provide the column type in some manner e.g. perhaps even just by inspecting the data type being passed? e.g. if it's a string value, assume NVarChar(len), if int assume Int, if float presume Float etc.

William Bell · Accepted Answer · 2020-09-18 03:00:31Z

1

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}`);

edited Sep 18, 2020 at 3:00

answered Sep 18, 2020 at 2:54

William Bell

413 bronze badges

2 Comments

Allister Over a year ago

Can you write a bit about what's happening in this code that makes it superior to the other answer?

William Bell Over a year ago

yes, the other examples did not work. the main enhancement is table.rows.add( values[j][0], values[j][1], values[j][2], values[j][3] );

Abdul Akibu · Accepted Answer · 2020-10-28 17:51:01Z

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}`);

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related