Search
Monday, November 12, 2018 ..:: Home ::.. Register  Login
   Calendar  
     
  
   Search  
     
  
   Blogroll  
     
  
   Disclosure  
All blog entries are the opinions of the author and do not necessarily reflect the opinions of their employer. All the code presented is for explanation and demonstration purposes only. Any damages incurred to your site and/or data are not the responsibility of the author. Every effort is taken to ensure the code properly compiles, however sometimes there are some hiccups and you might be required to do your own debugging.
     
  
   TechTidBits (Blog)  

You just inserted a row in SQL Server db now how do you get the PK\ID from it?

Mar 2

Written by:
Sunday, March 02, 2014 11:21 PM  RssIcon

TSqlFundamentalsYou just added\inserted data into your db, what PK\ID\counter\integer\whatever did you just add?  I can help you with three options.

Last week we were talking about entering data into our db.  There’s different ways of doing this of course, entity framework, ado.net, stored procs, etc, but in the end……..it always amounts to a SQL insert statement and in most cases, your table will have a PK\ID on the table and 1) you didn’t supply a value and 2) you want that value back!  HOW the heck do you get it?

You COULD add some type of string field, and then do a where clause…..haha yes, I’m kidding.  You wouldn’t want to do that in a prod system!  (for noobs, cause you could find some OTHER row which isn’t the one you put in, you want some guaranteed unique value, something like the Primary Key).

Cut to the chase (it’s late and I’m getting tired), you could call either @@identity, scope_identity() or ident_current(‘YourTableName’).  Pinal Dave does a GREAT article, but the highlights are:

ident_current(‘YourTableNameGoesHere’)

  • last ID created
  • on ANY connection and from ANY session

=> good for monitoring, but not really for a programmer\developer in a LOB application trying to add then update data in a couple of objects\tables

 

@@identiy =

  • last ID created
  • on your connection\session
  • includes inserts created from triggers, functions, etc
  • no matter what table it was created in

=> so you KNOW what PK ID you have but you don’t REALLY know what table it came from (sounds like the Seinfeld Car Reservation skit)

 

scope_identity() =

  • last ID created,
  • on your connection\session
  • limited to “things” you wrote\created

=> so although you may not explicitly know where it came from, you can infer from your sql what table it is from (just look at the last insert LOL)

 

So, chances are, if you need to get that last PK\ID out, you’re looking to select scope_identity().  Now that you know how to get that unique PK, it’

 

Resources

Journey to SQL Authority with Pinal Dave: SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

Tags:
Categories:
Location: Blogs Parent Separator TechTidBits

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel 
     
  
Copyright 1999-2012 by PCHenry.com   Terms Of Use  Privacy Statement