CODEWITHSUNDEEP
sql-servernode.jsazureexpressazure-sql-database
yn1043
yn1043

Reputation: 588

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

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

Upvotes: 3

Views: 247

Answers (1)

Jason A. Long
Jason A. Long

Reputation: 4442

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

Upvotes: 1

Related Questions