question

YogeshJ avatar image
YogeshJ asked

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

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 ?

sql server 2012sql server 2008 r2temporary-tableidentity-columninsert-exec-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

Whilst I agree this seems odd, the identity is not a constraint, it is a property, and can easily be duplicated. I would suggest that you explicitly state that skey has a unique constraint, or seeing that you have already said that it cannot be null, set it as a primary key constraint.

I'll keep searching to see if there's an documented explanation as to why the behaviour has changed

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·

I checked on SQL2012, and it's not that it doesn't allow duplicates - it simply doesn't allow you to insert into the identity column without explicitly turning identity_insert ON. What seems to be the behaviour from SQL 2014 is that using INSERT INTO ... EXECUTE is implicitly turning identity_insert on

1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.