SQL Server 2008 R2: IDENTITY -

SQL Server 2008 R2: IDENTITY -

q 1: have empty table insert records. having 1 column of identity type, want 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 employee values(101,'abc','xyz','highstreet','moscow')

error:

msg 8101, level 16, state 1, line 1 explicit value identity column in table 'employee' can specified when column list used , identity_insert on.

q 2:

how latest inserted id of employee without using max , top?

answer 1

if seek insert value identity column error

cannot insert explicit value identity column in table ‘employee’ when identity_insert set off.

write set identity_insert table name on before insert script , set identity_insert table name off after insert script

set identity_insert employee on insert employee(id,lastname,firstname,address,city) values (101,'abc','xyz','highstreet','moscow') set identity_insert employee off

answer 2

there several ways using after insert statement

after insert, select into, or mass re-create statement completed, @@identity contains lastly identity value generated statement

select @@identity

it returns lastly identity value produced on connection, regardless of table produced value, , regardless of scope of statement produced value.

select scope_identity()

it returns lastly identity value produced on connection , statement in same scope, regardless of table produced value.

select ident_current(‘employee’)

it returns lastly identity value produced in table, regardless of connection created value, , regardless of scope of statement produced value. ident_current not limited scope , session; limited specified table. ident_current returns identity value generated specific table in session , scope.

msdn source

sql-server sql-server-2008-r2