Identity Insert and Table Variables – CONNECT issue

I was running some code the other day and was surprised by the result.

DECLARE @tmp TABLE (myID INT IDENTITY,MyChar VARCHAR(200))

INSERT INTO @tmp(MyChar) Values('Apple')
INSERT INTO @tmp(MyChar) Values('Peach')
INSERT INTO @tmp(MyChar) Values('Pear')

DELETE FROM @tmp WHERE myID = 2

SET IDENTITY_INSERT @tmp ON

INSERT INTO @tmp(MyID, MyChar) Values(2,'Banana')

SELECT * FROM @tmp

My result?

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near
‘@tmp’.

Line 9 is the SET IDENTITY_INSERT statement.

I can understand why this is an issue. If you set this on a table variable, you could cause problems in other parts of the DB, since this can only be set on one table, but perhaps that’s not a huge issue? I don’t know, but either the functionality or documentation is wrong.

I submitted this on Connect. Vote if you agree.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

4 Responses to Identity Insert and Table Variables – CONNECT issue

  1. @sqlchow says:

    “If you set this on a table variable, you could cause problems in other parts of the DB, since this can only be set on one table”- table variables do have objects associated with them in tempdb right? If that is the case then how would having identity insert against a table variable going to affect other objects in the current DB? I am a little confused, could you kindly clarify?

  2. way0utwest says:

    Are table variables in tempdb? I’m not sure. If they’re in tempdb, I wouldn’t see an issue here, assuming the scoping in tempdb works well. I was assuming table variables would be noted in the database somewhere, which might be incorrect.

  3. Kevin Boisits says:

    These are compile time errors and are caused because it does not like the syntax.

    Even if you successfully SET IDENTITY_INSERT ON you would then get this error:

    Msg 1077, Level 16, State 1, Line 12
    INSERT into an identity column not allowed on table variables.

    I don’t see anywhere in the documentation that implies that you can use SET IDENTITY_INSERT on a table variable.

  4. way0utwest says:

    There’s nowhere it’s prohibited, or there wasn’t. SQL as a language allows constructions that should work consistently. The fact that this isn’t documented one way or the other is a problem.

    I submitted a Connect request to either fix this or change the documentation.

Comments are closed.