INSERT ... SELECT Into Multiple Tables

Hi,

I have a 'charges' table that records charges for an invoice. There are several different types of charges, each with its own unique set of additional data fields that need to be recorded.

I maintain separate tables for each charge type and these tables participate in an "ISA" relationship with the main charges table.

Here is a simplified version of my schema. Hourly charges are one type of charge:

charges table
=============
id int (autoincremented primary key)
date datetime
amount money

hourly_charges table
====================
charge_id int (primary key, also a foreign key to charges table)
start_time datetime
end_time datetime

I need to write a query that will duplicate all charges meeting a certain criteria by inserting new records into both the charges table and the hourly_charges table.

Here is some non-working pseudo-code that hopefully will get across what I would like to accomplish:

INSERT INTO charges JOIN hourly_charges
(
charges.date,
charges.amount,
hourly_charges.charge_id,
hourly_charges.start_time,
hourly_charges.end_time
)
SELECT
date,
amount,
SCOPE_IDENTITY(),
start_time,
end_time
FROM charges
JOIN hourly_charges
ON charges.id = hourly_charges.charge_id
WHERE some condition is true

Now I realize this code is invalid and I'll have to go about this an entirely different way but I'm wondering if someone can tell me what the proper way is.

Thanks,

Adam Soltys
http://adamsoltys.com/