DotNet Mirror
  DNM facebook   DNM Google+   DNM Twitter   

SET IDENTITY_INSERT for Table Variable

By Ashok Nalam on 09 Jan 2014 | Category: SQL Server | Tagged: identity variable table 
  Discuss Add Comment   |  
Add rating Rate this resource   

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.

  Discuss Add Comment    
Add rating Rate this resource   
About the Contributor
Member Since : 10 Dec 2012
Member Points (Level) : 9526  (Professional)
Location : INDIA
Home Page : http://dotnetmirror.com
About : I am admin of this site.
Rate this resource
 
Add your Comment
Name Email WebSite
Captcha Refresh


Comments (2)
 
1. By ZAP on 23 Oct 2023

 
2. By ZAP on 23 Oct 2023

 
cheap jordans|wholesale air max|wholesale jordans|wholesale jewelry|wholesale jerseys