ymasri
asked on
Retrieving the auto-generated number
Hi geeks,
Actually I'm quite new to this, so your patience is much appreciated.
When inserting certain record that has a field as auto-generated number, one may need to return this number for later association with other records in other tables. For example; sometimes it's impossible to distinguish the record except from this number, so if inserted, there will be no way to get back to the record unless this auto-generated number is captured right after the insertion process.
In oracle, (I don't know really if it's oracle specific or not!) I found that they provide some sort of routine(maybe it's pl/sql, I don't know) on which you can invoke ".nextval" to insert the result as an ID to the record, then invoke ".currval" to get that ID for programming use. Frankly, I don't know if I can do such thing in MS SQL server or even how to do it, so I appreciate if you can help me in this.
I saw somewhere else that after insertion of certain record they do something like:
"SELECT @@identity"
I really don't know how such thing works, or even if it could be used in MS SQL server, but I'm in favor of using such thing (or at least that what I feel from the syntax), because when dealing with concurrent invocations to database, one user may insert a record using
"recordnum.nextval" for example, and during insertion process, another may insert another record before the first has finished, so if the first one invokes
"recordnum.currval" he will take the wrong ID.
To brief, I have two questions:
1. Can we use [routine].nextval in MS SQL server? If so, is it safe to use it (see case above)? If yes, can you please tell me how [routine] can be created in MS SQL server.
2. Is there anything called "SELECT @@identity" in MS SQL server? Do I need special instructions when creating the database to be able to use it, like saying: "set this field as identity or auto number, etc."? Does it suffer from the concurrent invocation problem exists in case of [routine].nextval?
Thank you very much for your help.
Actually I'm quite new to this, so your patience is much appreciated.
When inserting certain record that has a field as auto-generated number, one may need to return this number for later association with other records in other tables. For example; sometimes it's impossible to distinguish the record except from this number, so if inserted, there will be no way to get back to the record unless this auto-generated number is captured right after the insertion process.
In oracle, (I don't know really if it's oracle specific or not!) I found that they provide some sort of routine(maybe it's pl/sql, I don't know) on which you can invoke ".nextval" to insert the result as an ID to the record, then invoke ".currval" to get that ID for programming use. Frankly, I don't know if I can do such thing in MS SQL server or even how to do it, so I appreciate if you can help me in this.
I saw somewhere else that after insertion of certain record they do something like:
"SELECT @@identity"
I really don't know how such thing works, or even if it could be used in MS SQL server, but I'm in favor of using such thing (or at least that what I feel from the syntax), because when dealing with concurrent invocations to database, one user may insert a record using
"recordnum.nextval" for example, and during insertion process, another may insert another record before the first has finished, so if the first one invokes
"recordnum.currval" he will take the wrong ID.
To brief, I have two questions:
1. Can we use [routine].nextval in MS SQL server? If so, is it safe to use it (see case above)? If yes, can you please tell me how [routine] can be created in MS SQL server.
2. Is there anything called "SELECT @@identity" in MS SQL server? Do I need special instructions when creating the database to be able to use it, like saying: "set this field as identity or auto number, etc."? Does it suffer from the concurrent invocation problem exists in case of [routine].nextval?
Thank you very much for your help.
there is no function or stored procedure or like in oracle - sequences - to do this job. if you want you can write your own. however the best and safest method is to use the identity property of the field and use @@IDENTITY variable.
ASKER
Thank you ibro for your response.
Forgive my ignorance, but what does it mean to say identity(1,1)? It sounds something like declaring the "id" as an identity, but I don't know what is it to say "(1,1)"?
You didn't address the problem of concurrent invocation of the database, I think with using @@IDENTITY, the problem still exists. Isn't there any solution from the database view?
Forgive my ignorance, but what does it mean to say identity(1,1)? It sounds something like declaring the "id" as an identity, but I don't know what is it to say "(1,1)"?
You didn't address the problem of concurrent invocation of the database, I think with using @@IDENTITY, the problem still exists. Isn't there any solution from the database view?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well, for the control over concurrent invocations, SQL Server, as well as Oracle both do more than just a "control" ;) ( wink ) If 2 users insert 2 records and the insert is too close in time or if some really neat trick is needed, like generate and retrieve the @@identity / sequence while insert is in progress, it will return a run time error and a message, message reads, identity undefined. On the other hand, identity is a function that can be used in a select list to generate values for an insert into for example temp table. There are restrictions applied onto table/column that hold identity values, there can be only 1 column per table holding identity values. That can be serious limitation if you insert result of a complex join into a temp table, where one of the clummns is an identity in the base table, and need to rank the rows using identity function as a value in a select list.
--- begin SQL pseudo code
select
identity( int, 0, 1 ), --- try to rank on teh fly
t1.PK_base_table_idemtity,
t2.name,
t3.address,
t2.phone
into #temp_table
from
table_1 as t1
inner join
table_2 s t2 on t1.ID = t2.ID_ref
inner join
table_3 as t3 on t3.ID_ref = t2.ID
where name like '%foo%'
order by t2.phone, t1.PK_base_table_idemtity desc
The statement will fail with error. Error message reads ( in - exact ) only one identity column allowed per table. In all other respects - which sufficses 99.9999 of normal database life identity works perfect. By the way in the SQL Server 2000 a great wealth of functions appeared that makes identity even more robust. IDENT_CURRENT() retrieves last identity generated for a particular table IDENTITY_SCOPE will return identity generated in current block of Transact SQL code.
--- begin SQL pseudo code
select
identity( int, 0, 1 ), --- try to rank on teh fly
t1.PK_base_table_idemtity,
t2.name,
t3.address,
t2.phone
into #temp_table
from
table_1 as t1
inner join
table_2 s t2 on t1.ID = t2.ID_ref
inner join
table_3 as t3 on t3.ID_ref = t2.ID
where name like '%foo%'
order by t2.phone, t1.PK_base_table_idemtity desc
The statement will fail with error. Error message reads ( in - exact ) only one identity column allowed per table. In all other respects - which sufficses 99.9999 of normal database life identity works perfect. By the way in the SQL Server 2000 a great wealth of functions appeared that makes identity even more robust. IDENT_CURRENT() retrieves last identity generated for a particular table IDENTITY_SCOPE will return identity generated in current block of Transact SQL code.
ASKER
Thank you miron for your contribution, I'm sure it's a reliable solution that works 99.9999% of the cases.
@@IDENTITY is a global variable, which holds the last inserted identity (auto-generated number) for the last insert. you can assign in a viariable and it is valid unitil the next autogenerated insert. So for example if you have the code:
create table tst (id int identity (1,1), val varchar(10) )
go
delcare @lastid int
insert inot tst (val) ('val1')
insert inot tst (val) ('val2')
set @lastid=@@IDENTITY
print @lastid
lastid variable will be 2, but not one