Not able to store newly inserted identity value into temp table using OUTPUT Clause

Question

I tried this way to store all newly inserted identity type value by OUTPUT clause.

CREATE TABLE #tmptblSectionLineItemTemplate
(
    ID INT Identity
)


BEGIN --INSERTNG/UPDATING Unique Section into tblSectionLineItemTemplate
    MERGE INTO tblSectionLineItemTemplate Trg
    USING 
    (
        SELECT TOP 100 PERCENT s.SectionID AS SectionID, l.ID AS LineItemID
        FROM (SELECT DISTINCT Section,LineItem FROM #TmpTenQKData) a RIGHT OUTER JOIN TblLineItemTemplate l ON a.LineItem = l.LineItem 
        RIGHT OUTER JOIN tblSectionTemplate s ON a.Section = s.Section
        WHERE s.TickerID=@TickerID AND l.TickerID=@TickerID 
        ORDER BY s.OrderID,l.ID

    ) AS Src 
    ON ISNULL(Trg.SectionID,0) = Src.SectionID AND ISNULL(Trg.LineItemID,0) = Src.LineItemID

    WHEN NOT MATCHED THEN 
        INSERT 
        (
            SectionID,
            LineItemID,
            Active,
            InsertDate,
            UserID
        )
        VALUES 
        (
            Src.SectionID, 
            Src.LineItemID, 
            'A', 
            GETDATE(), 
            @UserID
        )
        OUTPUT inserted.SectionDetl_Id  INTO #tmptblSectionLineItemTemplate(ID);
END 

SELECT * FROM #tmptblSectionLineItemTemplate

When i execute the above code then i am getting this error. Cannot insert explicit value for identity column in table 'tblSectionLineItemTemplate' when IDENTITY_INSERT is set to OFF.

Please tell me where i made the mistake. How can i store all newly inserted identity value of target table tblSectionLineItemTemplate into temp table?

Thanks

Answer 1

You didn't give us the DDL for the table you are merging into (tblSectionLineItemTemplate). But I'm assuming that table has a column named SectionDetl_Id which is declared to be an identity column. If that is correct, all you need to do is remove the identity property from the temp table. That is declare it as

 CREATE TABLE #tmptblSectionLineItemTemplate
 (
     ID INT
 )

Then your Output clause will put the new identity values from your main table (tblSectionLineItemTemplate) into your temp table.

Tom

Answer 2

I don't think you understand that identity is never a column: it's a table property! I also see that you tibble. This refers to the design flaw of putting the affix "tbl" in a table name. It violates the basic design principle that a data element name should not include metadata. If we had some DDL, knew what columns reference which columns, what the keys were, and the datatypes, then we might be able to help you more. This is why it's been required for basic SQL forum netiquette for about 30 years.