How to return Inserted records from a bulk insert query in SQL Server using Nodejs?

I have been working on a multi-row insert query for node-mssql and found that the bulk query is the most feasible, however there is not a way to return the inserted rows.

I know bulk is for a ton of rows, so I'm thinking I need to use a query, but I need direction with the query along with parameterized querying.

Bulk Docs

const table = new sql.Table('tasks');
table.create = true;
table.columns.add('name', sql.VarChar, {
  nullable: false
});
table.columns.add('department', sql.VarChar, {
  nullable: true
});
table.columns.add('details', sql.VarChar, {
  nullable: true
});

for (const task of tasks) {
  table.rows.add(task.name, task.department, task.details);
}

let result;
if (table.rows.length > 0) {
  result = await request.bulk(table);
}
return result; // All that is returned is the row count per documentation

Any help is appreciated.

Solution

To get the records inserted in a bulk insert query in MSSQL using Node.js, you can use the output method of the Request class of the mssql library. This method allows you to specify the columns you want to return after the insert.

const table = new sql.Table('tasks');
table.create = true;
table.columns.add('name', sql.VarChar, {nullable: false});
table.columns.add('department', sql.VarChar, {nullable: true});
table.columns.add('details', sql.VarChar, {nullable: true});

for (const task of tasks) {
  table.rows.add(task.name, task.department, task.details);
}

const request = new sql.Request();
request.bulk(table, (error, result) => {
  if (error) {
    console.error(error);
    return;
  }

  // Obtain the inserted records
  const insertedRows = result.rowsAffected.reduce((total, count) => total + count, 0);

  // Perform a query to obtain the inserted records
  request.query(`SELECT TOP ${insertedRows} * FROM tasks ORDER BY id DESC`, (error, result) => {
    if (error) {
      console.error(error);
      return;
    }

    // The inserted records are located in result.recordset
    console.log(result.recordset);
  });
});

I hope that help you.