CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Sarfaraz Makandar
Sarfaraz Makandar

Reputation: 6733

SQL Server 2008 R2: IDENTITY

Q 1:
I have a empty table to insert records. Having one column of IDENTITY type, for which I want to insert values manually.

Example:

Table: Employee

create table Employee
(
  ID int IDENTITY(1,1) PRIMARY KEY,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255)
 )

Inserting records:

SET IDENTITY_INSERT Employee ON;
insert into Employee values(101,'ABC','XYZ','HighStreet','Moscow')

Error:

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'Employee' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Q 2:

How to get latest inserted ID of an Employee without using MAX and Top?

Upvotes: 0

Views: 1771

Answers (3)

Dgan
Dgan

Reputation: 10285

Answer 1

If you will try to insert the value into Identity column you will get the error

Cannot insert explicit value for identity column in table ‘Employee’ when IDENTITY_INSERT is set to OFF.

Write SET IDENTITY_INSERT table name ON before the insert script and SET IDENTITY_INSERT table name Off after insert script

SET IDENTITY_INSERT Employee ON
    insert into Employee(ID,LastName,FirstName,Address,City) values   
    (101,'ABC','XYZ','HighStreet','Moscow')
SET IDENTITY_INSERT Employee OFF

Answer 2

There are several ways using like this after insert statement

After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement

SELECT @@IDENTITY

It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.

SELECT SCOPE_IDENTITY()

It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.

SELECT IDENT_CURRENT(‘Employee’)

It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

MSDN SOURCE

Upvotes: 2

Navneet
Navneet

Reputation: 447

For external value of Identity column Column Name given explicitly is Must as below

SET IDENTITY_INSERT Employee ON;
insert into Employee(ID ,LastName,FirstName,Address,City)
values(101,'ABC','XYZ','HighStreet','Moscow')

Upvotes: 0

Chief Wiggum
Chief Wiggum

Reputation: 2934

You can use IDENT_CURRENT( 'table_name' ) to get the current value.

Upvotes: 0

Related Questions