[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

How to get the last record from a table and insert it into another table?

I need to get the last info_id record from my Info table and insert it into my Files table.  Info_id is an auto number identity Column.
How would I do this?
0
jettman26
Asked:
jettman26
  • 3
  • 3
1 Solution
 
raterusCommented:
Is this right after you have inserted it?  If you are using SQL Server, you can access this value through the @@IDENTITY or Scope_Identity() functions.

"Insert into Info (field1) values 'blah'; Select @info_id = @@IDENTITY"

Use that as your sql, and just pull the parameter @info_id out of the command, then you have that to do whatever you want.  

Also, you might want to look into a trigger when a record is added into the Info table.

--Michael
0
 
jettman26Author Commented:
Thanks for the information.
It could be right after I insert the record into Info table.  
I used IDENT_CURRENT('INFO') and it worked rather well.

I have never used a trigger before.  Not sure if It will work in my situation.
0
 
Anthony PerkinsCommented:
>>I used IDENT_CURRENT('INFO') and it worked rather well.<<
If it is in the same session, than this is not a good idea.  Use Scope_Identity() or if you are using SQL Server 7 use @@IDENTITY instead.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
The reasons is that in a multi-user environment, two users could get the same IDENTITY value.
0
 
jettman26Author Commented:
I would have used Scope_Identity() but I don't think that will work for me, since after I save my INFO record, I may do multiple Inserts to my FILES table.
In this case, it is uploading multiple files and writing their information to the Files table and then getting the info_id of the last record in the INFO table and Inserting it into that record of the FILES table.
0
 
Anthony PerkinsCommented:
SCOPE_IDENTITY will always return the last IDENTITY added in the current session.  But if you believe that there is no risk that two users will execute the same code with IDENT_CURRENT('INFO') simultaneously, than you should not have any problems.  If you are not sure, than it will probably be Murphy's Law.
0
 
jettman26Author Commented:
Thanks for helping.  If you are bored, I have other posts unanswered  :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now