How to update a Autonumbered field from one table to another???

[Guest_imported]

Hi,
I am new to SQL server 2000.
I have 2 tables both of which contain a field called COMPID.
I am using vbscript in Asp, to update one table using the value from the other.The COMPID is primary key and Autonumber in the first table.In the seocnd one it is not autonumbered.
Since the script doesn't know what the COMPID will be since it is autonumber,I want to knwo whether it is possible to use it to update the second table COMPID field with the same value.
Appreciate any help regarding this.
Thanks,
Shalu.

 

[tlbroadbent]

Yes, you can insert the column from table1 into table2 or update the column in table2 with the value from table1. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cmmrfrds]

Are you trying to add the primary table record and secondary table records in the same script. If so, you can capture the primary id by doing
Select @COMPID = @@identity
immediately after you insert the record in the primary table.

 

[Guest_imported]

Hi Cmmrfrds,

Yes I am using the same script but in different db connections.The select statement you wrote is working I think.But I am getting some other errors.I am trying to solve it.If not then I will let you know more details.
Thank you very much for your help.Since I had no idea as to how to do it this helped me a lot.

Shalu.

 

[miked123]

Can a Stored Procedure for updating the parent table return the identity of the inserted record? Then the procedure for updating the child can use it.

Total SP written 0.
Thanks.

 

[cmmrfrds]

miked123, you would just need to pass the identity field as a parameter to the second store procedure. Select the identity into a variable and pass the variable to the SP.