Objects in this mirror are closer to Microsoft Technologies. DNM objective is to help users on Microsoft technologies by providing Articles, snippets, Interview Questions.

31 August 2012

SET IDENTITY_INSERT for Table Variable in SQL Server


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

SET IDENTITY_INSERT ON/OFF works for talbes and temporary table.
we can not use for table variables.

If we run a sample query below
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
 
INSERT INTO @student(ID,Name) Values(2,'E')

we will get error as
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '@student'



0 Comments:

Post a Comment