SET IDENTITY_INSERT for Table Variable

Introduction:

SET IDENTITY_INSERT - Allows to insert explicit values to identity column of the table.

SET IDENTITY_INSERT ON/OFF works only for Tables and Temporary table and we can not use IDENTITY_INSERT for table variables. Below query shows what is the outcome when we try to set IDENTITY_INSERT for table variable.

DECLARE @student TABLE (ID INT IDENTITY,Name VARCHAR(50))
 
INSERT INTO @student(Name) Values('A')
INSERT INTO @student(Name) Values('B')
INSERT INTO @student(Name) Values('C')
INSERT INTO @student(Name) Values('D')
 
DELETE FROM @student WHERE ID = 3
 
SET IDENTITY_INSERT @student ON --error-hint: Incorrect sytax near '@student'. Expecting '.',ID, or QUOTED_ID
 
INSERT INTO @student(ID,Name) Values(2,'E')

Output:

Msg 102, Level 15, State 1, Line 10

Incorrect syntax near '@student'.

Summary:

We can not use SET IDENTITY_INSERT for table variables.