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

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Adam Machanic (amachanic_at_air-worldwide.nospamallowed.com)
Date: 05/04/04 

Date: Tue, 4 May 2004 10:27:27 -0400

Check out @@IDENTITY in Books Online.

Also, please note that the DBMS is the system that runs the databases
(Database Management System), and Microsoft created it (SQL Server) --
You've created a database within the DBMS. Just syntax, but it's very
important that we all speak the same language in order to fully understand
one another.

"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: read from file or mysql
    ... >> They way i see it, it is the database management system that allows to ... >> One could say that a filesystem is a dbms for files. ... a database doesn't store data. ... And the database is stored by the database management system. ...
    (comp.lang.php)
  • Re: read from file or mysql
    ... >> True database adds overhead over file read operations when we read ... And the database is stored by the database management system. ... DBMS and when a File System". ... By the context of matter i think it is clear where i am talking about ...
    (comp.lang.php)
  • Re: Good Books on MultiValue Databases
    ... have to admit I was intrigued by the multivalue database, however, I ... In SQL-relational land you 'talk' to the DBMS via SQL commands. ... Do not be confused about this: PickBasic is not a scripting language. ...
    (comp.databases.pick)
  • Re: Size matters to some
    ... BobJ wrote: ... specified to the DBMS tools with a maxlen specification, ... database does, without the maxlen specification to the DBMS. ... Field A is supposed to be 2 characters of a country abbreviation, ...
    (comp.databases.pick)
  • Re: Object oriented database
    ... By 'dbms' do you mean VAX DBMS? ... line from digital's database folks to be incredibly interesting. ... It made input from files with repeating groups "look ... rdbms has this where the actual implementation of the database is ...
    (comp.databases.theory)