CODEWITHSUNDEEP
sqlsql-server
DarioN1
DarioN1

Reputation: 2552

SqlServer - Insert multiple records and get new and old ID

I have this query:

CREATE TABLE [factOffertDetail](
    [idOffertRow] [INT] IDENTITY(1,1) NOT NULL,
    [idOffertRegion] [INT] NOT NULL,
    [idProduct] [INT] NOT NULL,
    [Qty] [DECIMAL](12, 2) NULL,
    [idUnitPrice] [TINYINT] NULL
    )

DECLARE @TMP2 TABLE (
    idOffertRowNEW INT,
    idOffertRow INT
    )

INSERT INTO factOffertDetail
        ( idOffertRegion ,
          idProduct ,
          Qty ,
          idUnitPrice 
            )
    OUTPUT inserted.idOffertRow INTO @TMP2(d.idOffertRowNEW)
    SELECT 
        d.idOffertRegion,
           d.idProduct ,
           d.Qty ,
           d.idUnitPrice
    FROM factOffertDetail d

I need to get the keys of the old and the new idOffertRow generated by identity.

idOffertRow is the identity (1,1) key of the factOffertDetail table.

How can I do this with an insert ?

Is it possible or I have to switch to merge command ?

Thanks to support

Upvotes: 1

Views: 83

Answers (1)

Tharindu Madushanka
Tharindu Madushanka

Reputation: 100

I would recommend to doing this:

Alter your table with new coloum,

ALTER TABLE [factOffertDetail]
ADD [ParentId] [INT] NULL

then,

INSERT INTO factOffertDetail
(   ParentId,
    idOffertRegion ,
    idProduct ,
    Qty ,
    idUnitPrice 
    )
OUTPUT inserted.idOffertRow,inserted.ParentId INTO @TMP2(idOffertRowNEW,idOffertRow)
SELECT 
    d.idOffertRow,
    d.idOffertRegion,
    d.idProduct ,
    d.Qty ,
    d.idUnitPrice
FROM factOffertDetail d

Thank You!

Upvotes: 1

Related Questions