Re: complex inserting

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

From: Anith Sen (anith_at_bizdatasolutions.com)
Date: 11/01/04 

Date: Mon, 1 Nov 2004 11:34:56 -0600

Renjith,

When you have an identity column in a t-SQL table, SCOPE_IDENTITY() function
can return the newly inserted identity column value within the the same
scope ( Note that, in previous versions @@IDENTITY was used which had global
scope ). So, for instance when you do:

INSERT Invoices ( col1, col2.. )
VALUES ( val1, val2,... );
SELECT SCOPE_IDENTITY()
-- will return the newly inserted value for InvoiceGID

In a well modularized stored procedure, you can extract the newly inserted
indentity value in a local variable & insert into the referencing columns in
other tables like CompanyInvoice, BranchInvoice & so forth.

There are other related functions like IDENT_CURRENT which gives the last
identity value generated for a specified table in any session and any scope.
You can find all the details about @@IDENTITY, SCOPE_IDENTITY() and
IDENT_CURRENT along with specific examples, if you spend some time with SQL
Server Books Online. 

-- 
Anith

Relevant Pages

  • Re: How do I tell which row I just inserted?
    ... "SCOPE_IDENTITY returns values inserted only within the current scope; ... "For example, you have two tables, T1 and T2, and an INSERT trigger defined ... insert statements into an identity column occur in the scope." ... Inc. www.solien.com "Mike Labosh" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • SCOPE_IDENTITY() returns NULL
    ... My program executes INSERT on a table with an identity column and ... In the BOL I found this: ... insert statements into an identity column occur in the scope. ...
    (microsoft.public.sqlserver.programming)
  • Re: Incrementing Table Error?
    ... The dynamic SQL has it's own scope, and variables do not exist outside the ... T-SQL by using two @ signs, as there are no global variables in T-SQL. ...
    (microsoft.public.sqlserver.programming)
  • Re: Remove Identity attribute
    ... Identity is a property that cannot be modified through t-sql. ... Enterprise Manager, the system will take care of it for you by creating a ... > I have a table with its PK as an identity column. ...
    (microsoft.public.sqlserver.programming)
  • Remove Identity attribute
    ... I have a table with its PK as an identity column. ... I want to alter the table ... T-SQL? ... Thanks & Regards ...
    (microsoft.public.sqlserver.programming)