identity column

Hi. I have a table with an identity column on it as the pk.
When I do my insert, is there a way I can find out immediately what was the
identity value for this insert, so I can insert the same value into another
table?
Thanks in advance.
Alex IvascuAre you familiar with the options of @.@.identity, scope_identity(), and
ident_current()?
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Alex Ivascu" <alexdivascu@.sbcglobal.net> wrote in message
news:kLwlb.5411$Xp6.599@.newssvr27.news.prodigy.com...
> Hi. I have a table with an identity column on it as the pk.
> When I do my insert, is there a way I can find out immediately what was
the
> identity value for this insert, so I can insert the same value into
another
> table?
> Thanks in advance.
> Alex Ivascu
>
>|||Nope. But I would appreciate any help, Brian. Since I see you're online -
might as well take advantage of this.
I'm working on designing an app that should support multiple databases (sql
and oracle for now), that has a Java front-end.
For sql server, are identity columns the best way to go for pk? Or, should
I generate my own pk's?
Alex Ivascu
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%230KGQxKmDHA.2268@.TK2MSFTNGP12.phx.gbl...
> Are you familiar with the options of @.@.identity, scope_identity(), and
> ident_current()?
>
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Alex Ivascu" <alexdivascu@.sbcglobal.net> wrote in message
> news:kLwlb.5411$Xp6.599@.newssvr27.news.prodigy.com...
> > Hi. I have a table with an identity column on it as the pk.
> >
> > When I do my insert, is there a way I can find out immediately what was
> the
> > identity value for this insert, so I can insert the same value into
> another
> > table?
> >
> > Thanks in advance.
> >
> > Alex Ivascu
> >
> >
> >
>|||You can create a trigger and check the value of value of identity column in
the newly inserted row.
Just Example:
create table xtry
(
id int identity,
name varchar(20)
)
create trigger xtry_trigger
on xtry
for insert
as
declare @.var int
select @.var = id from inserted
print @.var
insert into xtry (name) values ('x')
"Alex Ivascu" <alexdivascu@.sbcglobal.net> wrote in message
news:kLwlb.5411$Xp6.599@.newssvr27.news.prodigy.com...
> Hi. I have a table with an identity column on it as the pk.
> When I do my insert, is there a way I can find out immediately what was
the
> identity value for this insert, so I can insert the same value into
another
> table?
> Thanks in advance.
> Alex Ivascu
>
>|||> You can create a trigger and check the value of value of identity column
in
> the newly inserted row.
> declare @.var int
> select @.var = id from inserted
This assumes that only one row could ever be inserted at one time! Not
true!
insert xtry(name) select 'foo' union all select 'bar'
How about sticking with SCOPE_IDENTITY() (or @.@.IDENTITY in 6.5/7.0)?|||You are 100% right.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:exDoH3KmDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > You can create a trigger and check the value of value of identity column
> in
> > the newly inserted row.
> > declare @.var int
> > select @.var = id from inserted
> This assumes that only one row could ever be inserted at one time! Not
> true!
> insert xtry(name) select 'foo' union all select 'bar'
> How about sticking with SCOPE_IDENTITY() (or @.@.IDENTITY in 6.5/7.0)?
>|||or the other way could be to put the value of identity column from the last
row of the INSERTED table (in case it has multiple records)
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:exDoH3KmDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > You can create a trigger and check the value of value of identity column
> in
> > the newly inserted row.
> > declare @.var int
> > select @.var = id from inserted
> This assumes that only one row could ever be inserted at one time! Not
> true!
> insert xtry(name) select 'foo' union all select 'bar'
> How about sticking with SCOPE_IDENTITY() (or @.@.IDENTITY in 6.5/7.0)?
>|||> or the other way could be to put the value of identity column from the
last
> row of the INSERTED table (in case it has multiple records)
Even still, why do you need the overhead of a trigger, to perform something
that's already built-in? Also, how is the app going to retrieve the value
from the trigger? Are you going to create another table to stuff the
MAX(id) FROM inserted into?