Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a main table with an autonumber key. I need to introduce a new table, related to the main table in a one-to-many relationship (i.e. a record in the main table could conceivably link to several records in the new table). If the new table links to the main table via that autonumber key, then I (or the user) has to supply the corresponding number for each new record in the new table. Is there a way to automate any aspect of this, so the user doesn't have to keep cross-checking this number when he makes a new entry?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Most brands provide methods to retrieve the key an insert created in an identity or auto_increment column.

Unfortunately there is no general method for this, it will depend on the brand.

For example in MySQL it's called LAST_INSERT_ID(), in Transact SQL (SQLServer) @@identity (better: scope_identity()).

share|improve this answer
1  
SCOPE_IDENTITY() is better to use in SQL Server than @@IDENTITY. @@IDENTITY returns the latest identity used where as SCOPE_IDENTITY() limits that to the current scope. technet.microsoft.com/en-us/library/ms190315.aspx –  Wind Raven Mar 13 at 13:08
    
True, i was just given examples though, in order to use them correctly, he'd have to read some basics anyway. Will still edit :) –  Sascha Rambeaud Mar 13 at 13:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.