Subscribe to this blog
Briefcase
Print
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
Loading comments...