I HAVE A SERIOUS PROBLEM

Question

I HAVE A SERIOUS PROBLEM

sukhoi971

SSCarpal Tunnel

Points: 4833
More actions
May 20, 2004 at 2:09 am

#160940

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]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AH SSC Veteran Points: 267 More actions · Answer 1

Use scope_identity() - will give you the last identity value inserted into an identity column within the same scope ie the procedure / trigger etc....

Peter P SSCrazy Points: 2777 More actions · Answer 2

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

AH SSC Veteran Points: 267 More actions · Answer 3

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

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 4

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]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 6

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]

Peter P SSCrazy Points: 2777 More actions · Answer 7

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.