Can I SELECT information the same time i INSERT

[Robby]

I want to know if there is a way to get the PK from a row the minute I insert information. Here's the scenario, what's the most efficient way of doing this? 1. I have 50 tables that represent 50 HTML forms. 2. I am inserting results from OnlineAccess.asp into dbo.OnlineAccess. 3. I am keeping all dateTime stamps along with the status of the request (OPEN, BEING PROCESSED, COMPLETE) in another table called dateTime. 4. I want to get the PK of the information entered into dbo.OnlineAccess so that I can insert this into dbo.DateTime for relation to dbo.OnlineAccess's information. Is there any "bi-di" capabilities that can be used to return the PK without having to run another line of code to SELECT the PK WHERE . For instance, I know I can do it this way, but it seems like too much leg work: sqlInsert = "INSERT into OnlineAccess(userID,lastName,firstName,...) values('"&userID&"','"&lastName&"','"&firstName&"',...)" sqlSelect = "SELECT requestID FROM OnlineAccess WHERE userID='"&userID&"' AND lastName='"&lastName&"' AND ..." RS = MyConn.Execute(sqlSelect) requestID = RS("requestID") sqlInsert = "INSERT into DateTime (requestID,status) values('"&requestID&"','OPEN')" (I have a trigger that takes care of the dateTime stamps based on the status) Can anyone help me on this one? Thanks in advance! Robby

[David Salter]

Probably the best way would be to write a stored procedure that can then insert your data and then return the PK to the caller. I know this works with SQL Server, but I've had trouble returning values from SPs in Oracle. Dave.

[Jon Hulatt]

Indeed, using a stored procedure would by far and away be the best solution. after an insert, sql server special variable @@IDENTITY contains the new pk of what you just inserted. so this will probably work:-

sqlInsert = "INSERT into OnlineAccess(userID,lastName,firstName,...) values('"&userID&"','"&lastName&"','"&firstName&"',...) ; SELECT @@IDENTITY AS [newkeyname]"

RS = MyConn.Execute(sqlInsert)
requestID = RS("newkeyname")

But really thats a bag of sh!t of an implementation. Stored procedures are the way to go. Why? 1. stored procedures run much quicker, because they are precompiled. 2. They are like functions in proper languages; you can change the internal code without changing the interface. You could most probably do the whole job in one SP, like this:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [p_YourProc]
@UserId int,
@Firstname varchar(100),
@Lastname varchar(100),
@NewPKout integer output

AS

-- define a sql variable
declare @NewPK int;

-- do the main insert first.
INSERT into OnlineAccess(userID,lastName,firstName) values
( @UserId, @LastName, @Firstname);

-- retrieve the new key
select @NewPK=@@IDENTITY;

-- do the other inserts
INSERT into DateTime (requestID,status)
values(@NewPK,'OPEN')

-- might as well send the pk back to asp
set @NewPKout = @NewPK

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

There. Bob's your uncle. Signature space for rent. Apply by email to....