Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

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.


Filed under: Blog Tagged: syndicated, T-SQL

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...