Usage of @@Identity spanning two databases

Posted on 2011-10-04
Last Modified: 2012-05-12
Hi all,

Am developing an application where the data is saved/selected in/from two different databases say:

db-B has a procedure that uses tables from db-A like so:
SELECT * FROM db-A.dbo.table1
and so on

Its working find for SELECTION sprocs but now I have a requirement to add through a SPROC which returns/selects an @@Identity at the end. That is, when the insertion is made I want the ID of the record inserted to use in the rest of the code.

The problem:
The insertion procedure is part of db-B, say SPROC-db-B but it is using the tables from db-A for insertion purposes. This is handled as stated above (db-A.dbo.table1 etc) but when it comes to the @@Identity part (at the end of SPROC-db-B), it gets the last record (I presume) of the db where the SPROC is contained, i.e. db-B.

I simply need the @@Identity to return the last record inserted in the db-A.
Is that possible using "Identity"? Or any other method?
Let me know if further info is required.

Question by:ChilliSauce
    LVL 15

    Accepted Solution

    and if this doesn't work create also a stored in the other database that will do the insert and return the SCOPE_IDENTITY() back

    Author Comment

    If you mean something like:
    db-A.dbo.SCOPE_IDENTITY() then its not working, I have already tried that.

    We also don't have the option to use the db-A to create a new sproc that returns the Identity. The purpose here is to use the db-B's procedure to insert into db-A's tables (success) and return the @@Identity of the record inserted in db-A (no success).

    LVL 9

    Expert Comment

    SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

    Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch
    Return Types

    IDENT_CURRENT( 'table_name' )
    Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

    IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope

    SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

    LVL 9

    Expert Comment

    The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
    LVL 26

    Assisted Solution


    As sachinpatil10d mentioned you should use SCOPE_IDENTITY() instead of  @@IDENTITY cause there may be other transaction doing insertions...

    And simple usage of the SCOPE_IDENTITY()

    DECLARE @id   int

    INSERT INTO db_a.dbo.tablex (.....)
    VALUES (........)



    Author Comment

    What you saying is that if I use SCOPE_IDENTITY() in my case i.e.
    in a sproc of dbo-B, I use dbo-A tables for insertaion, will this function return the insertion row number for dbo-A.table1?

    If that is the case then I will try this and let you know.

    LVL 75

    Expert Comment

    by:Anthony Perkins
    It would be so much easier if you posted your code.  We could then quit guessing and give you the solution.

    Author Closing Comment

    Thanks everyone. The Scope_Identity() helped.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now