Getting the value that was generated using the IDENTITY property

From: Novice (6tc1ATqlinkDOTqueensuDOTca)
Date: 05/04/04 

Date: Tue, 4 May 2004 07:21:11 -0700

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: 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: Storing Users/Groups
    ... websites can interface with this setup. ... > returns @outtable table (root_node int, group_key int, primary key ... > --40 members are members of 50 ... PRIMARY KEY, ...
    (microsoft.public.sqlserver.programming)
  • Re: Getting the value that was generated using the IDENTITY property
    ... If you have SQL Server 2000, ... 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: Storing Users/Groups
    ... returns @outtable table (root_node int, group_key int, primary key ... --40 members are members of 50 ... PRIMARY KEY, FOREIGN KEY REFERENCES, ... Is it still possible to query all users of the websites with this additional functionality? ...
    (microsoft.public.sqlserver.programming)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I'm just trying to differentiate between two fundamentally different SQL objects. ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ... SELECT MAXFROM nestedview ...
    (comp.databases.ms-sqlserver)