[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Server IDENT_CURRENT

Posted on 2013-06-10
5
Medium Priority
?
597 Views
Last Modified: 2013-06-10
I am trying to find out what my last primary key was on a table, but I am getting the following error.  I need to know that last key so that I can insert a new record with next correct primary key value

SELECT IDENT_CURRENT(dbo.Person)

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Person" could not be bound.

nick
0
Comment
Question by:countyprob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 39235820
Try using single quotes

select ident_current('dbo.Person')
0
 
LVL 1

Author Comment

by:countyprob
ID: 39235941
Thanks Gohord, that worked, except I'm returning a null.  After looking a the table, my last row shows PK_PersonID as 700940.  I was going to use this value to increment by 1 and then insert new value into the table.   Is my approach wrong or is this how a DBA typically inserts the next record?  Sorry for asking dumb question, all my DBA's left.  

nick
0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 2000 total points
ID: 39235971
I usually don't use that method but it can work depending on the situation.  If I'm looking for a quick and dirty one time only approach, I'll use that.

Most of the time, I'll take a different approach.  For example, if my code is inserting a record via T-SQL, I'll return the @@identity as a result of a call to an insert stored procedure.  

For example,

declare @myNewId int

insert values into my table

select @myNewId = @@Identity  --this will return the new value of the identity field.

select @myNewId
0
 
LVL 1

Author Closing Comment

by:countyprob
ID: 39235982
Thanks, I appreciate the help
0
 
LVL 10

Expert Comment

by:Banthor
ID: 39236001
I would recommend using SCOPE_IDENTITY() vs @@Identity
because: if there is a trigger on a table that inserts a row on a second table
The second table id value is the @@identity returned.  

IDENT_CURRENT is giving me some very unexpected results.
return 1 for a table with a thousand rows
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question