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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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
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. – WindRaven Mar 13 '14 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 '14 at 13:34

Your Answer


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.