Solved

insert get identity - does not return correct number

Posted on 2014-09-03
3
149 Views
Last Modified: 2014-09-03
Hello,
I have a table with identity column (Id).  I.e.   MyTable(Id int, MyNum int, MyText varchar(50), CreatedBy int)
I insert into it like this   MyTable(MyNum, MyText)values(3,'Mm')
Then I want to get the identity of the newest column.  I do it like this:
SELECT @@Identity.
The newest number in the identity column is 20993, but I get returned 1005.
I do not understand why this is. Can somebody please help.
0
Comment
Question by:johnson1
[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
3 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40301582
There could be a trigger on the table.  @@identity is the last one for any table in any scope.

Use function SCOPE_IDENTITY() instead; or use the OUTPUT clause on the INSERT to get the identity value.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40301586
DECLARE @identity TABLE (
    id int
    )

INSERT INTO MyTable(MyNum, MyText) OUTPUT INSERTED.$IDENTITY INTO @identity values(3,'Mm')
0
 

Author Closing Comment

by:johnson1
ID: 40301752
Thank you very much. There was a trigger.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

738 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