I am wondering how to do this outside of a trigger. I have to do an INSERT into table A. After that, I run another INSERT into table B.

However, I will need the ID of the row I just INSERTed into table A for the table B INSERT.

Previously, when a trigger was the way to go, I would use the INSERTED temp table, but a trigger isn't applicable in my situation. Any ideas?

Thanks in advance.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

Try using rowid
SELECT ROWID, Firtsname from customer

I haven't had a chance to test it yet, but I have this so far:

BEGIN TRANSACTION newtrans
    INSERT INTO tblA(col1,col2,...)
    VALUES(value1,value2,...)

    SELECT @NewID = MAX(id) FROM tblA
COMMIT TRANSACTION newtrans

Now I think I should be able to use @NewID in my other statements, right?

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

The ROWID is not a valid function for SQL Server.

You can use the @@IDENTITY function to retrieve the last assigned identity value:
Be aware that if there is a trigger on the insert table, and the trigger also does an insert into a table containing an IDENTITY column, the @@IDENTITY function will reflect that INSERT, and not your original one. You can use the scope_identity() function in that case.

Your example will also work, but could be slow unless there is an index on the ID column.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff-

In your code:
declare @TheID integer
INSERT yourtable (<columns>,...)
    VALUES (<somevalues>,...);

set @TheID=@@IDENTITY

INSERT anothertable (<columns>,...)
    VALUES (@TheID,<morevalues>,...);

Does the line set @TheID=@@IDENTITY by default refer to the table most recently INSERTed? Would I be able to write
set @TheOtherID=@@IDENTITY at the end of your code block and get the value for anothertable?

I ask because in the procedure I am writing, there will be 2 times I need to use @@IDENTITY.

Thanks again,
-Colonel

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

The @@IDENTITY function returns the last value assigned to an identity column in any table inserted into on the current connection.

So yes, every time you read it, it contains the last value inserted regardless of which table was inserted into.

You can retrieve the value of the @@IDENTITY as many times as you want.

Note that there is also the IDENT_CURRENT('<tablename>') function, which returns the last assigned identity value on the indicated table, but this can be tricky to use, as it retrieves the last value in any scope or connection.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thank you Jeff. You're a gentleman and a scholar.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.