syntax error table variable

Question

This is the code:

declare @Ids table ( Id int identity(1,1));

SET IDENTITY_INSERT @Ids ON;

and I get:

Incorrect syntax near '@Ids'

I cannot see what's wrong. Any ideas? Thanks.

Answer 1

You can't use SET IDENTITY_INSERT on table variables

This works

CREATE TABLE Ids ( Id int identity(1,1))
SET IDENTITY_INSERT Ids ON

and this

CREATE TABLE #Ids ( Id int identity(1,1))
SET IDENTITY_INSERT #Ids ON