I’m using node-mssql
3.2.0 and I need to INSERT INTO
a table and return the id of the inserted record.
I can successfully use sql.Transaction()
to insert data, but the only parameters given to callbacks (request.query()
and transaction.commit()
) are:
const request = new sql.Request(); request.query('...', (err, recordset, affected) => {}); const transaction = new sql.Transaction(); transaction.commit((err) => {});
So recordset
is undefined
for INSERT
, UPDATE
and DELETE
statements, and affected
is the number of rows affected, in my case 1
.
Does anyone know a good way to obtain an inserted records id (just a primary key id) after a transaction.commit()
using node-mssql
..?
Advertisement
Answer
Instead of just doing an INSERT INTO...
statement, you can add a SELECT...
statement as well:
INSERT INTO table (...) VALUES (...); SELECT SCOPE_IDENTITY() AS id;
The SCOPE_IDENTITY()
function returns the inserted identity column, which means recordset
now contains the id
:
const request = new sql.Request(); request.query('...', (err, recordset, affected) => {});
I don’t think request.multiple = true;
is required, because although this includes multiple statements, only one of them is a SELECT...
and so returns.
So the answer was SQL related and is not specific to node-mssql
.