Getting the identity of some newly created row

If I have an account table where the column "accountno" is an identity
i.e. auto-generated, and I insert a new row like so (sorry, but all-
caps keywords are so 70s):
insert account
values (20000.00, 42)
and then want to do something with the newly created account rows
accountno, like insert it in another table, like:
insert account
values (20000.00, 42)
insert transactions
values (accountno, 20.000)
except this of course does not work. Maybe something like:
insert account
values (20000.00, 42)
declare @.accountno int
select @.accountno = ?
insert transactions
values (accountno, 20.000)
Help!Check out Scope_Identity in BooksOnLine.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<pinkfloydhomer@.gmail.com> wrote in message
news:1194105832.364153.128820@.o38g2000hse.googlegroups.com...
> If I have an account table where the column "accountno" is an identity
> i.e. auto-generated, and I insert a new row like so (sorry, but all-
> caps keywords are so 70s):
> insert account
> values (20000.00, 42)
> and then want to do something with the newly created account rows
> accountno, like insert it in another table, like:
> insert account
> values (20000.00, 42)
> insert transactions
> values (accountno, 20.000)
> except this of course does not work. Maybe something like:
> insert account
> values (20000.00, 42)
> declare @.accountno int
> select @.accountno = ?
> insert transactions
> values (accountno, 20.000)
> Help!
>|||SELECT @.@.IDENTITY
Returns the last-inserted identity value
From http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||No it doesn't in some cases. Use @.@.IDENTITY instead. Check out
Scope_Identity in BOL against @.@.IDENTITY and you will see why..
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||Use SCOPE_IDENTITY unless you have a compelling reason to use @.@.IDENTITY.
See BOL for the differences.
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Getting the identity of some newly created row

SELECT @.@.IDENTITY
Returns the last-inserted identity value.
From http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
No it doesn't in some cases. Use @.@.IDENTITY instead. Check out
Scope_Identity in BOL against @.@.IDENTITY and you will see why..
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
|||Use SCOPE_IDENTITY unless you have a compelling reason to use @.@.IDENTITY.
See BOL for the differences.
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

getting newly created id number(asap)

hello,

i have a problem in my sql. im using a stored procedure and i want to get the newly created id number to be used to insert in the other table.

it works like:

insert into table() values()

select @.id = (newly created id number) from table

insert into table1() values(@.id)

something like that.

Hi lenerd,

You should usescope_identity for this!

Here are some links, please give it a look and try!

http://msdn2.microsoft.com/en-us/library/ms190315.aspx
|||

Hi lenerd3000

its too easy, i hope you know what command object is. execute scalar will return the primary key of newly generated record.

dim myID as integer =CType(myCommand.ExecuteScalar(),Integer)

hope it helps