http://www.sqlservercentral.com/blogs/steve_jones/2012/08/03/identity-insert-and-table-variables-connect-issue/

Printed 2014/10/30 06:32AM

Identity Insert and Table Variables – CONNECT issue

2012/08/03

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.


Filed under: Blog Tagged: syndicated, T-SQL
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.