Home Dashboard Directory Help
Search

SET IDENTITY INSERT on table variables by way0utwest


Status: 

Closed
 as By Design Help for as By Design


12
4
Sign in
to vote
Type: Bug
ID: 757012
Opened: 8/3/2012 12:41:34 PM
Access Restriction: Public
0
Workaround(s)
view
7
User(s) can reproduce this bug

Description

When using a table variable, the SET IDENTITY INSERT command errors out with

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@tmp'.


Test harness:
DECLARE @tmp TABLE (SlNo INT IDENTITY,Name VARCHAR(200))

INSERT INTO @tmp(Name) Values('SCREW')
INSERT INTO @tmp(Name) Values('HAMMER')
INSERT INTO @tmp(Name) Values('SAW')

DELETE FROM @tmp WHERE SlNo = 2

SET IDENTITY_INSERT @tmp ON

INSERT INTO @tmp(SlNO,Name) Values(2,'SHOVEL')

SELECT * FROM @tmp
Details
Sign in to post a comment.
Posted by Microsoft on 2/12/2013 at 1:25 PM
It's not just IDENTITY INSERT. Table variables cannot be altered. Added that information to the table (Transact-SQL) topic. It will appear in a couple weeks. Thank you for submitting this item.
Posted by Wayne Sheffield on 8/31/2012 at 7:38 AM
My up vote is for clarifying the documentation at http://msdn.microsoft.com/en-us/library/ms175010 to include this in the limitations and restrictions section.
Posted by Wayne Sheffield on 8/31/2012 at 7:32 AM
If it is meant to not work with table variables, it ought to be documented here: http://msdn.microsoft.com/en-us/library/ms175010 (table (Transact-SQL)) with the other limitations of table variables.
Posted by Steve Knox on 8/31/2012 at 12:48 AM
This is a tough one, because it could be an error in one of two very different places.

Neither the SQL server documentation for table variables (http://msdn.microsoft.com/en-us/library/ms175010) or for SET IDENTITY_INSERT (http://msdn.microsoft.com/en-us/library/ms188059) states that you can't SET IDENTITY_INSERT ON for a table variable.

So does this mean that you should be able to and that this is a software bug?

Or is the software working as designed and this is a documentation bug?

In either case, the error message is not very helpful -- in the first case you should not get an error message, and in the second case, the error message should state that you cannot SET IDENTITY_INSERT ON for a table variable, rather than giving a generic syntax error.
Sign in to post a workaround.