May 20, 2004 at 2:09 am
IAM USING 1 STORED PROCEDURE TO UPDATE 2 TABLES
1 IS MASTER TABLE
AND 2ND IS THE CHILD TABLE
MASTERS TABLE PRIMARY KEY IS AN IDENTITY COLUMN
AND THAT CHILD KEY IS USING THAT Id AS A REFERENCE
NOW WHAT HAPPENS WHEN I INSERT A RECORD INTO THE TABLE I DONT KNOW THE VALUE OF THE "ID" COLUMN WHICH IS IDENTITY "AUTO INCREMENT"
WHICH I WANT TO KNOW TO INSERT THE ID VALUE INTO THE CHILD TABLE
ANY IDEA ABOUT KNOWING THAT VALUE ?
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 20, 2004 at 3:01 am
Use scope_identity() - will give you the last identity value inserted into an identity column within the same scope ie the procedure / trigger etc....
May 20, 2004 at 3:04 am
Hi,
You can use @@IDENTITY to obtain the inserted ID value:
INSERT INTO MASTERTABLE(val1,val2)Values('hello','world')
declare @MyID int
Select @MyID = @@IDENTITY
INSERT INTO CHILDTABLE(ForeignKey,val1)Values(@MyID,'value')
Good Luck
May 20, 2004 at 3:31 am
I always prefer to use the scope_identity() - see example below for the reasons why.
There are times when you may want to use @@identity to return the last inserted identity value from the trigger but just be careful in the one that you use as there are differences.
SET NOCOUNT ON
go
if exists( select * from sysobjects where name = 't1' and type = 'u')
drop table t1
if exists( select * from sysobjects where name = 't2' and type = 'u')
drop table t2
go
create table t1(f1 int identity(1,1), f2 varchar(20))
create table t2(f1 int identity(1,1), f2 varchar(20))
go
--insert some data
insert t1 select 'a'
insert t1 select 'b'
insert t1 select 'c'
go
if exists( select * from sysobjects where name = 'test1' and type = 'p')
drop procedure test1
go
create trigger testtrig on t1 for insert
as
insert t2 select 'a'
go
create procedure test1
as
insert t1 select 'd'
--will return the last identity value inserted within the scope of the procedure
select 'scope_identity' = scope_identity()
--will return the resultant identity value inserted by trigger
select '@@identity' = @@identity
go
exec test1
go
SET NOCOUNT OFF
go
May 20, 2004 at 3:43 am
that was great for the this i declare a cursor and got the max value and assumed that this is the latest identity value, thats great u ppl solved my problem
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 20, 2004 at 5:38 am
Sukhoi,
I don't know if the little bit of "language barrier" between us is a problem or if I'm just misreading this... just want to make sure you aren't stepping onto a slippery rock...
If you are saying that you used to use a cursor to get the max value and then assume that's the record and that you decided that that is no longer the correct way to do it... then you did a good thing.
If you are saying that you decided to use a cursor to get the max value and then assume that's the record as the correct way to get an identity... then that's a bad thing.
--Jeff Moden
May 20, 2004 at 6:12 am
No dude, iam saying u r suggestion of using @@identity is the best 1 and iam using it,
but before that i was using 1 cursor to get the maxvalue from the main header table after inserting the record it was working, but it was not the very correct way to do things,
RIGHT.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 20, 2004 at 10:42 am
Sukhoi, I have never had a problem using @@IDENTITY in the way that I described. However, please don't dismiss Jeff's suggestion of using scope_identity(). It can replace @@IDENTITY in the same way, and is best if any of the tables that you are modifying have triggers.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply