Next Number

Question

Post reply

Next Number

Dinesh Asanka

SSChampion

Points: 11058
More actions
January 27, 2004 at 1:34 am

#106868

Is there anyway that I can get the next number of the identity field BEFORE inserting a record.
My need is this.
My master table has one record and detail table has one/more record with relation to the master record.
When I do a insert into one will write to master record and I want that number to be enterd in the detail record

My Blog: http://dineshasanka.spaces.live.com/

Viewing 11 posts - 1 through 10 (of 10 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

I don't know of a way to get this value before inserting without querying the system tables.

However, wouldn't SCOPE_IDENTITY() also do the job?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 2

Thankx for the answer. That will be my last option If I didn't get any other solutions.

Ur solution is ok.

But problem existing when there are several users entering into same table.

My Blog: http://dineshasanka.spaces.live.com/

gljjr SSChampion Points: 10080 More actions · Answer 3

As Frank stated I would use the scope_identity() function. If you are doing the inserts within a SP then this not a problem at all for multi-user scenarios. Simply insert the parent record, get the identity value and then insert the child record(s).

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 4

Thankx for the answer

Actually I'm doing this via VB. Is there any way of doing it

My Blog: http://dineshasanka.spaces.live.com/

gljjr SSChampion Points: 10080 More actions · Answer 5

If you are doing this from VB then I would suggest calling the sp with all paramters for both records. Then have the sp do all the inserts. If it fails then you can roll back the transaction and raise an error to VB. This only requires one round trip to the database. Plus you gain transactional error checking.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 6

Ok,

But my problem is with getting next number of identity column

My Blog: http://dineshasanka.spaces.live.com/

jarretg SSCommitted Points: 1706 More actions · Answer 7

To answer your question, you can do this:

select IDENT_CURRENT('TABLE_NAME') + IDENT_INCR('TABLE_NAME')

IDENT_CURRENT() will return the last identity value generated for the table and the IDENT_INCR() will return the increment value for the table. When you add them together, that will be the next value to be generated.

However, this will cause a problem in your database if this scenario happens.

Connection one gets the next identity value and sends it back to the application. Connection two then comes in before the insert has been made by connection one and gets the next identity value. This value will be the same as connection one's. Connection one will insert its child records based on the value it retrieved. Connection two will also insert its child records. Since the values retrieved by both connections are the same, all of the child records inserted will be pointing to the parent record that connection one inserted. The parent record from connection two will be lonely.

Your best bet is to use the SCOPE_IDENTITY() function like Gary and Frank have suggested.

Jarret

guarddata SSCertifiable Points: 6583 More actions · Answer 8

Yeah - you don't want to use IDENT_CURRENT because of multi-user problems. I would follow the suggestions and use the @@IDENTITY or SCOPE_IDENTITY() in the first stored procedure and pass the value back through an output variable that your VB application can read. This will allow inserting multiple child records which could not be done using a single stored procedure.

Rollback still works fine as long as you execute the begin transaction call on your connection object from within VB before the first stored procedure call. Commit or Rollback when all the processing is done. ... Of course, you will want this to be as short a time as possible.

My $0.02

Guarddata-

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

Just to add my .02 to this...

I feel this is kind of backwards, and not very pretty, handling different parts of the transaction in the client layer (VB)

What you really should do (as suggested earlier) is to call a procedure with the parameters,

and have the proc handle it all (in T-SQL) regarding identities and child records et al.

It will make your life easier in the end (or the DBAs at any rate )

=;o)

/Kenneth

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 10

Great, That is ok with me

Thank you guys

My Blog: http://dineshasanka.spaces.live.com/