[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 learn.microsoft.com/en-us/sql/t-sql/queries/… — Dan Guzman
– Dan Guzman, Commented Sep 3, 2017 at 13:01
@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
– yn1043, Commented Sep 3, 2017 at 14:01
@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
– SMor, Commented Sep 4, 2017 at 2:52

Jason A. Long · Accepted Answer · 2017-09-04 17:16:44Z

1

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

edited Sep 4, 2017 at 17:16

answered Sep 4, 2017 at 3:02

Jason A. Long

4,4421 gold badge14 silver badges18 bronze badges

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

13 Comments

yn1043 Over a year ago

Thank you.This seems to require an IDENTITY column.But Our Table don't have that.I do not know if that is the reason, but the following error will occur."The target table of the OUTPUT INTO clause cannot have any enabled triggers"

Jason A. Long Over a year ago

It does not... I've updated my example... I removed the IDENTITY(1,1) property and added hand coded ID values to the insert.

yn1043 Over a year ago

Thank you. hmm... in my case,the id is assigned when inserting... so I cant pass the id to query...

Jason A. Long Over a year ago

What version of SQL Server are you using?

yn1043 Over a year ago

Thank you. it works properly.I can get the id!!By the way, Why should we Drop and Create these table again??Before doing that,it didn't work...

|

Collectives™ on Stack Overflow

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

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related