Skip to content

Identity Insert and Table Variables – CONNECT issue

August 3, 2012

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 these ads

From → Blog

4 Comments
  1. @sqlchow permalink

    “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. 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 permalink

    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. 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.

Follow

Get every new post delivered to your Inbox.

Join 4,551 other followers

%d bloggers like this: