Solved

SQL Server IDENT_CURRENT

Posted on 2013-06-10
5
569 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
  • 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Reporting Services Service Start Timeout 4 48
SqlAdvisor 2016 3 28
SQL 2008 Conversion failed 7 20
SQL Statement to Update Email Domain 2 21
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now