[Azure/mssql]How do I get Insert query recordset

Question

I'm using mssql. After inserting a record,I want to get the id of the data. But I don't know hot to do that. My code is below. please give me answer.

var mssql = require('mssql');
mssql.connect(config.mssql, function(err) {
var request = new mssql.Request(); 
request.query('insert -----'),function(err, data) {
   console.log(data);
}

Insert worked properly,but console log is [undefined] ....

this is the ddl of the table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Feature](
[id] nvarchar NOT NULL CONSTRAINT [DF_Feature_id] DEFAULT (CONVERT(nvarchar,newid(),(0))),
[createdAt] datetimeoffset NOT NULL CONSTRAINT [DF_Feature_createdAt] DEFAULT (CONVERT(datetimeoffset,sysutcdatetime(),(0))),
[updatedAt] datetimeoffset NULL,
[version] [timestamp] NOT NULL,
[deleted] [bit] NULL DEFAULT ((0)),
[title] nvarchar NULL,
[text] nvarchar NULL,
[period_from] datetimeoffset NULL,
[period_to] datetimeoffset NULL,
[priority] [float] NULL,
PRIMARY KEY NONCLUSTERED
(
[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

GO

For a singleton insert, you could add SELECT SCOPE_IDENTITY() after the INSERT statement in the same batch. Alternatively, specify an OUTPUT clause (OUTPUT inserted.ID)), which can also handle multi-row inserts. See docs.microsoft.com/en-us/sql/t-sql/queries/… — Dan Guzman
@DanGuzman Thank you for responding.I tried, but didn't work .request.query('insert -----;SELECT SCOPE_IDENTITY()),function(err,data).But data is undefined. — yn1043
@YoshihideNishimoto Post ddl for the table involved - at least the columns that are part of the primary key. Presumably your table has an identity column - do you have a different type of synthetic key? — SMor

Jason A. Long Jason A. Long · Accepted Answer · 2017-09-04T03:02:39

Use the OUTPUT clause. Here's an example of the syntax...

IF OBJECT_ID('RandomTest.dbo.FeatureID_Capture', 'U') IS NOT NULL
DROP TABLE dbo.FeatureID_Capture;
GO      
CREATE TABLE dbo.FeatureID_Capture (
    Id NVARCHAR(50)
    );
GO

IF OBJECT_ID('RandomTest.dbo.Feature', 'U') IS NOT NULL
DROP TABLE dbo.Feature;
GO  
CREATE TABLE dbo.Feature (  
    id NVARCHAR(40) NOT NULL
        CONSTRAINT DF_Feature_id
        DEFAULT (CONVERT(NVARCHAR(40), NEWID(), (0))),
    createdAt DATETIMEOFFSET NOT NULL
        CONSTRAINT DF_Feature_createdAt
        DEFAULT (CONVERT(DATETIMEOFFSET, SYSUTCDATETIME(), (0))),
    updatedAt DATETIMEOFFSET NULL,
    version TIMESTAMP NOT NULL,
    deleted BIT NULL
        DEFAULT ((0)),
    title NVARCHAR (10) NULL,
    text NVARCHAR (10) NULL,
    period_from DATETIMEOFFSET NULL,
    period_to DATETIMEOFFSET NULL,
    priority FLOAT NULL,
    PRIMARY KEY NONCLUSTERED (id ASC)
    WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
    );
GO 
--=======================================================

INSERT dbo.Feature (title, text) 
    OUTPUT Inserted.id INTO dbo.FeatureID_Capture(Id)
VALUES ('t 1', 'txt 1'), ('t 2', 'txt 22'), ('t 3', 'txt 333');

-------------------------------------

SELECT *FROM dbo.FeatureID_Capture fic;

The output...

Id
--------------------------------------------------
4E9EB3CD-AD44-4837-9B87-BBB85308FFBF
B93983B6-C15A-4534-8AC4-EB9404964C09
FAFA678A-8416-490C-A871-3963EAB67B9F

[Azure/mssql]How do I get Insert query recordset

1 Answers