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