SCOPE_IDNTITY()

Hi All,
How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
appreciated.
Regards
Muralicreate table dbo.foo(id INT IDENTITY(1,1), name varchar(30))
go
insert dbo.foo(name) select 'bar'
select scope_identity()
go
drop table dbo.foo
go
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>|||A scope is a module -- a stored procedure, trigger, function, or batch.
Eg: from Books online.
This example creates two tables, TZ and TY, and an INSERT trigger on TZ.
When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts a
row in TY.
USE tempdb
GO
CREATE TABLE TZ (
Z_id int IDENTITY(1,1)PRIMARY KEY,
Z_name varchar(20) NOT NULL)
INSERT TZ
VALUES ('Lisa')
INSERT TZ
VALUES ('Mike')
INSERT TZ
VALUES ('Carla')
SELECT * FROM TZ
--Result set: This is how table TZ looks
Z_id Z_name
--
1 Lisa
2 Mike
3 Carla
CREATE TABLE TY (
Y_id int IDENTITY(100,5)PRIMARY KEY,
Y_name varchar(20) NULL)
INSERT TY (Y_name)
VALUES ('boathouse')
INSERT TY (Y_name)
VALUES ('rocks')
INSERT TY (Y_name)
VALUES ('elevator')
SELECT * FROM TY
--Result set: This is how TY looks:
Y_id Y_name
--
100 boathouse
105 rocks
110 elevator
/*Create the trigger that inserts a row in table TY
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS
BEGIN
INSERT TY VALUES ('')
END
/*FIRE the trigger and find out what identity values you get
with the @.@.IDENTITY and SCOPE_IDENTITY functions*/
INSERT TZ VALUES ('Rosalie')
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @.@.IDENTITY AS [@.@.IDENTITY]
GO
--Here is the result set.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope, which
was the insert on table TZ*/
@.@.IDENTITY
115
/*@.@.IDENTITY returned the last identity value inserted to TY by the trigger,
which fired due to an earlier insert on TZ*/
Thanks
Hari
SQL Server MVP
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>|||Read up about it in the BOL.
"Murali" <ivantagemurali@.gmail.com> wrote in message
news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>|||Example:
create table t1 (colA int not null identity unique)
go
create table t2 (colA int not null identity(100, 1) unique)
go
create trigger tr_t1_ins on t1
for insert
as
insert into t2 default values
go
insert into t1 default values
select scope_identity(), @.@.identity
go
drop table t1, t2
go
AMB
"Murali" wrote:

> Hi All,
> How to use the SCOPE_IDENTITY() in SQL Server, any example will be highly
> appreciated.
> Regards
> Murali
>
>|||Thank you very much Hari.
Regards
Murali
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uSMlykaRFHA.3972@.TK2MSFTNGP14.phx.gbl...
> A scope is a module -- a stored procedure, trigger, function, or batch.
> Eg: from Books online.
> This example creates two tables, TZ and TY, and an INSERT trigger on TZ.
> When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts
a
> row in TY.
> USE tempdb
> GO
> CREATE TABLE TZ (
> Z_id int IDENTITY(1,1)PRIMARY KEY,
> Z_name varchar(20) NOT NULL)
> INSERT TZ
> VALUES ('Lisa')
> INSERT TZ
> VALUES ('Mike')
> INSERT TZ
> VALUES ('Carla')
> SELECT * FROM TZ
> --Result set: This is how table TZ looks
> Z_id Z_name
> --
> 1 Lisa
> 2 Mike
> 3 Carla
> CREATE TABLE TY (
> Y_id int IDENTITY(100,5)PRIMARY KEY,
> Y_name varchar(20) NULL)
> INSERT TY (Y_name)
> VALUES ('boathouse')
> INSERT TY (Y_name)
> VALUES ('rocks')
> INSERT TY (Y_name)
> VALUES ('elevator')
> SELECT * FROM TY
> --Result set: This is how TY looks:
> Y_id Y_name
> --
> 100 boathouse
> 105 rocks
> 110 elevator
> /*Create the trigger that inserts a row in table TY
> when a row is inserted in table TZ*/
> CREATE TRIGGER Ztrig
> ON TZ
> FOR INSERT AS
> BEGIN
> INSERT TY VALUES ('')
> END
> /*FIRE the trigger and find out what identity values you get
> with the @.@.IDENTITY and SCOPE_IDENTITY functions*/
> INSERT TZ VALUES ('Rosalie')
> SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
> GO
> SELECT @.@.IDENTITY AS [@.@.IDENTITY]
> GO
> --Here is the result set.
> SCOPE_IDENTITY
> 4
> /*SCOPE_IDENTITY returned the last identity value in the same scope, which
> was the insert on table TZ*/
> @.@.IDENTITY
> 115
> /*@.@.IDENTITY returned the last identity value inserted to TY by the
trigger,
> which fired due to an earlier insert on TZ*/
> Thanks
> Hari
> SQL Server MVP
> "Murali" <ivantagemurali@.gmail.com> wrote in message
> news:%23eMyahaRFHA.1476@.TK2MSFTNGP09.phx.gbl...
highly[vbcol=seagreen]
>