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?
Add a comment
|
1 Answer
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()).
-
1SCOPE_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 Mar 13, 2014 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 :) Mar 13, 2014 at 13:34