March 10, 2006 at 12:09 pm
Hello all. I have a statement that creates a record with the first field (Counter) as the primary key (autogenerated number). I have a second statement that creates a second record, and I need to pull the primary key from the first record and insert it into the second record in a field called RefNum which allows the application to reference the first record.
Is there any way to get that Counter number and assign it to a variable? Can I just use ASP to assign a variable to the result of a SELECT, MAX, or LAST statement?
The second statement will run immidiately after the first, so the primary key will be the highest number in that column.
If the MAX or LAST statements will work, what is the necessary syntax? I have been trying, but unsuccessful.
Thanks,
Parallon
March 10, 2006 at 12:48 pm
have a look at the scope_identity() function
Cheers,
* Noel
March 10, 2006 at 12:49 pm
SELECT
@second_last_value_of_primary_key = column_name
FROM dbo.Table_name t
WHERE t.primary_key_column_name = @@IDENTITY - 1
/*Now update the most recently inserted row*/
UPDATE t
SET t.reference_column_name = @second_last_value_of_primary_key
FROM dbo.Table_name t
WHERE t.primary_key_column_name = @most_current_value_of_primary_key
Hope that helps...
March 10, 2006 at 3:11 pm
Thank you both. I will give it a try!
Mike
March 14, 2006 at 12:58 pm
Wow!
Look what it did to my post. I will try to re-post
Have a look at it now
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply