INSERT ... SELECT into multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT ... SELECT into multiple tables

Author

Topic

asoltys
Starting Member

8 Posts

Posted - 2008-04-07 : 13:42:33

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-07 : 13:49:33

Try with OUTPUT clause like this:-

INSERT INTO charges 
(
date, 
amount
)
OUTPUT INSERTED.id,other field values INTO hourly_charges
VALUES (yourdatevalue,youramountvalue)

asoltys
Starting Member

8 Posts

Posted - 2008-04-07 : 13:51:48

Thanks for the prompt response. I just looked up the OUTPUT clause and realized it's a feature of SQL Server 2005. Unfortunately I'm on 2000 (sorry, should have mentioned that initially).

Am I out of luck or is there a workaround?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-07 : 13:54:22

Change compatibility level to 90 if you are in 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources