Insert Into multiple tables

[jhall01]

I hope this is a fairly easy question:

I have 2 tables. A main table and a status table. Joined by an autogenerated ID key.

When Someone inserts a record into the main table I want it to set the status to 01 - Open which would date it in the other table based on the Primary Key,

Currently I have it setup as an Identity column so it generates the key automatically.

I am using a SQL insert insead of a <cfinsert>. Since the key isn't generated until the main table has a record inserted how do I get the key to flow to the status table?

What is the SQL syntax?

Assume these fields for simplicity:

main table
orderID, orderDate, order_nbr

status table
orderID, statuscode, status_def

Thanks in Advance!

Jon

 

[twcman]

Which db are you using?

Chris Scott
The Whole Computer Medical Systems

 

[jhall01]

SQL Server 2000. I figured out using local variables and @@identity to retrieve back the last entered record to pull out the ID field and use another Insert to insert the data into the second field.