INSERT new values from a recent INSERT

[cossiboon]

I hope the subject was somewhat descriptive.

I'm trying to INSERT records into a table, easy...

My insert statement is as follows:

INSERT INTO user_tbl
SELECT * FROM user_tbl WHERE user_id IN (SELECT user_id FROM list_user_index_tbl WHERE list_id = 11)

This is simple. I'm inserting the users that are in list_id 11. Now I want to grab the new user_id's (IDENTITY FIELD) from the newly inserted users and use those values back in the list_user_index_tbl with a different list_id value. list_user_index_tbl has only two fields user_id and list_id.

Making any sense?

any help would be great!

josh

 

[Nevermoor]

use a stored procdure to do the input, and after the insert statement, set a return value equal to @@identity.

on your asp page, use the following syntax:

Set cmd = Server.CreateObject("ADODB.COMMAND")
cmd.ActiveConnection = YOUR_CONN_OBJ
cmd.CommandType = 4
cmd.commandText = "MyProc(input1,input2)"
Response.write(cmd.commandText)
cmd.Parameters.Append Cmd.CreateParameter("RETURN_VALUE", 3, 4, 0)
cmd.Execute
myVal= cmd.Parameters("RETURN_VALUE").Value

then you'll have the new identity to use in your next insert

 

[cossiboon]

Thanks for the response!

The initial INSERT may insert more than one record. So I would need all the user_id's at once to insert them into the other table.

 

[Nevermoor]

There are a couple options that come to mind, although I haven't personally done any of them.

You could have the stored procedure return a table of the inserted elements and do a second insert select statement

You could have the stored procedure return the @@identity (I'm assuming it would be the last one) but count the number of records you are going to insert before actually doing it (i.e. select count(*) as recordsToDo from list_user_index_tbl WHERE list_id = 11) and get a range to loop through

I am a bit confused about what you are trying to do, though, it looks like you are duplicating records based upon them having list_id = 11?

 

[SQLSister]

First, let me explain that @@identity is dangerous to use and should be avoided as it willreturn an identity form a trigger on the table rather than the identity of the table you did the insert on.

Josh, you could do this. Grab the highest identity value before the insert and place it in a variable and then do a query selecting the records with a higher value in the id field. Make sure to put it all in a transaction so no one else will be inserted simlutaneously.

Alternatively you could put the selected recordsinto a temp table and then do the insert by joining on the temp table. Then use the temp table to identify which records were inserted.



Questions about posting. See faq183-874