question

Issue in MS2016 : In Identity Column of #Table while 'INSERT INTO EXEC SP' Statement

---------------------------------------------------------------------------

CREATE PROCEDURE SP_TestSP

AS

CREATE TABLE #AAA(SKEY INT NOT NULL,AAA INT)

INSERT INTO #AAA(skey,AAA)

SELECT 1,1212121

SELECT SKEY,AAA FROM #AAA

GO

CREATE TABLE #UnloadRuleUtilizeLimit ( skey INT IDENTITY(1,1) NOT NULL,KYCCategoryNo INT)

INSERT INTO #UnloadRuleUtilizeLimit(skey,KYCCategoryNo)EXEC SP_TestSP

INSERT INTO #UnloadRuleUtilizeLimit(skey,KYCCategoryNo)EXEC SP_TestSP

select *from #UnloadRuleUtilizeLimit --This code must give error as we are trying to insert explicit and duplicate value into IDENTITY column.

---------------------------------------------------------------------------------------------------

Correct Behavior(@@version = Microsoft SQL Server 2012 ) :-

-----------------------------------------------------------------------------------------

Msg 544, Level 16, State 1, Line 24 Cannot insert explicit value for identity column in table '#UnloadRuleUtilizeLimit__'

-----------------------------------------------------------------------------------------

Wrong Behavior (@@version = Microsoft SQL Server 2016 (SP1) ):-

------------------------------------------------------------------------------------------

skey KYCCategoryNo

----------- -------------

1 1212121

1 1212121

(2 row(s) affected)

-------------------------------------------------------------------------------------------

This is a issue!! Sql server does not honor the Identity constraint in case of #table only when we are inserting values in it using Exec command

Any one have any clue on this ?