DotNet Mirror
  DNM facebook   DNM Google+   DNM Twitter   

Insert data to table with only one IDENTITY column exists in table

By Ashok Nalam on 24 May 2013 | Category: SQL Server | Tagged: table identity insert 
If we have a table with only one column which is marked as IDENTITY column, then how we will insert data to table? This scenario will be very rare. But we will see how to insert data to table with only one IDENTITY column exists.
  Discuss Add Comment   |  
Add rating Rate this resource  [Rated 5.00/5 by 1 people] 

Introduction

If we have a table with only one column which is marked as IDENTITY column, then how we will insert data to table? This scenario will be very rare. But we will see how to insert data to table with only one IDENTITY column exists.

Table Schema:
CREATE TABLE tblOnlyIdentityCol(ID INT PRIMARY KEY IDENTITY(1,1))

Insert Data to Table:

 INSERT INTO tblOnlyIdentityCol DEFAULT VALUES;
 INSERT INTO tblOnlyIdentityCol DEFAULT VALUES;
 INSERT INTO tblOnlyIdentityCol DEFAULT VALUES;

Output: 1,2,3

Select last Inserted Data: 

In order to select the last inserted value we can fetch using 

SELECT SCOPE_IDENTITY();

Output: 3

Alternative way to insert Data to Table:
SET IDENTITY_INSERT tblOnlyIdentityCol ON ;
INSERT INTO tblOnlyIdentityCol(ID) VALUES(10)
INSERT INTO tblOnlyIdentityCol(ID) VALUES(20)
SET IDENTITY_INSERT tblOnlyIdentityCol OFF ;

Output: 1,2,3,10,21

If we run the SQL command  "INSERT INTO tblOnlyIdentityCol DEFAULT VALUES;" again, then next value will be inserted as 21.

  Discuss Add Comment    
Add rating Rate this resource  [Rated 5.00/5 by 1 people] 
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 (0)
No comments found, click here to add comment.
 
cheap jordans|wholesale air max|wholesale jordans|wholesale jewelry|wholesale jerseys