0

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?

2

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()).

  • 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 – RubberChickenLeader 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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