RE: Getting Identity Column value
From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 09/17/04
- Next message: Tim: "Re: HELP! VB.NET killed SQL Server"
- Previous message: RichK: "Importing Excel Workbook Into SQL Server Table Using VBA & OWC10.D"
- In reply to: Satya: "Getting Identity Column value"
- Next in thread: Dan Guzman: "Re: Getting Identity Column value"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 17 Sep 2004 05:49:11 -0700
Yes you can.
- To get last inserted in current scope (a stored procedure, trigger,
function, or batch) use function SCOPE_IDENTITY().
- To get the last not limited to a specific scope use @@IDENTITY
- To get the inserted into a specific table, no matter scope or session, use
function IDENT_CURRENT
Example:
create table tableA(colA int not null identity(1, 1))
create table tableB(colA int not null identity(100, 1))
go
create trigger tr_tableA_I on tableA
for insert
as
insert into tableB default values
go
insert into tableA default values
-- print the last identity inserted
-- in this case, the last value inserted in tableB because tableA has an
after insert trigger that insert into tableB
print @@identity
-- the last inserted in this batch or objects visible in this batch
print scope_identity()
-- last inserted into a specific table
print ident_current(tableA)
print ident_current(tableB)
go
drop table tableA, tableB
go
AMB
"Satya" wrote:
> Hai all,
>
> Is there any way to get the identiy col value that would be generated on a
> table if i inserted a row?
>
> Or put it another way can i get the last identity col value generated on a
> table?
>
> TIA
>
> Satya
- Next message: Tim: "Re: HELP! VB.NET killed SQL Server"
- Previous message: RichK: "Importing Excel Workbook Into SQL Server Table Using VBA & OWC10.D"
- In reply to: Satya: "Getting Identity Column value"
- Next in thread: Dan Guzman: "Re: Getting Identity Column value"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
