Re: Getting the value that was generated using the IDENTITY property

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 05/04/04 

Date: Tue, 4 May 2004 11:15:43 -0400

If you have SQL Server 2000, it's safer to use SCOPE_IDENTITY(). 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Vijay Mishra" <y_tyagi79@yahoo.com> wrote in message
news:%23jJo8leMEHA.2252@tk2msftngp13.phx.gbl...
You can use @@IDENTITY system variable which variable which will be
populated with the last identity value always.
Thanks
Yogesh
"Novice" <6tc1ATqlinkDOTqueensuDOTca> wrote in message
news:848C9E3F-4246-41D1-84BB-77D254531943@microsoft.com...
> Hi all, I've just created a DBMS using the IDENTITY property for the
primary key in a bunch of my tables.
>
> Now I'm writing the data layer to my application and am a little confused
as to how I get the primary key for a record/row that I just inserted.
>
> I mean here is a simplified version of my DB:
> ------------------------------------------------
> CREATE TABLE another_table
> (
>   id_num int IDENTITY(1, 1),
>   some_data ntext
> )
>
> CREATE TABLE new_employees
> (
>  id_num int IDENTITY(1,1),
>  fname varchar (20),
>  minit char(1),
>  lname varchar(30)
>  link_to_another_table int FOREIGN KEY REFERENCES another_table(UID)
>                 ON DELETE NO ACTION
>
>
> INSERT another_table
>    (some_data)
> VALUES
>    ('This is some data I put here')
>
> INSERT new_employees
>    (fname, minit, lname)
> VALUES
>    ('Karin', 'F', 'Josephs', ???????)
> ------------------------------------------------
> I mean obviously in this scenario I know that the value of the key will be
1 since I just created the table (and I've told it to start at 1 and
increment by 1).  But when I create the data layer in the application
(ADO.NET) is there some way to get the value of that id_num in the table
name "another_table" without using a SELECT statement?
>
> I guess what I'm really asking is if there is a way to do this:
> ------------------------------------------------
> INSERT another_table
>    (some_data)
> VALUES
>    ('This is some data I put here')
> ------------------------------------------------
> and to have the value of id_num returned (as if from a function that
returns an int value).
>
> Thanks,
> Novice

Relevant Pages

  • Re: Users , Web Application and SQL Server back end - Beginner Questio
    ... Create table Users (userid int not null primary key, ... Create table UserTypes (usertypeid int not null primary key, ... I have developed a java based Web application that is using SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: Users , Web Application and SQL Server back end - Beginner Que
    ... Create table Users (userid int not null primary key, ... I have developed a java based Web application that is using SQL Server ... admin just has access to some extra tables. ...
    (microsoft.public.sqlserver.security)
  • Re: Getting the value that was generated using the IDENTITY property
    ... SQL Server MVP ... as to how I get the primary key for a record/row that I just inserted. ... > id_num int IDENTITY, ... But when I create the data layer in the application ...
    (microsoft.public.sqlserver.programming)
  • Re: surely, theres got to be a better way
    ... Columnist, SQL Server Professional ... And he would redo my query without it. ... TABLE_A_id INT ... description NVARCHARTABLE_A.id and TABLE_A.date for the primary key in TABLE_A while TABLE_A_id and TABLE_A_date are foreign keys into TABLE_A Basically, I want a query that will give me everything from TABLE_A if it is NOT in TABLE_B what I came up with was: select id, date, name from TABLE_A where ) ...
    (microsoft.public.sqlserver.programming)
  • Getting the value that was generated using the IDENTITY property
    ... Now I'm writing the data layer to my application and am a little confused as to how I get the primary key for a record/row that I just inserted. ... id_num int IDENTITY, ... fname varchar, ...
    (microsoft.public.sqlserver.programming)