Inserting data from multiple tables

Hi

My code (below) works, but seems very long-winded. Is there a more efficient way of doing this? I'd be really grateful for any help.

I'm inserting (appending) data from tables (tblLoadMSV900_r, tblLoadMSV900_r, .., ..) into one table(tblInvoices):

Can I use one insert instead of two
(the case statements within each select are the same)?

Thanks
Sara

_______________________________

insert into [tblInvoices]
(full_period,
account_code,
tran_amount,
function,
capital,
revenue,
misc,
source)
select
full_period,
account_code,
tran_amount,
--set function depending on account_code
case
when left(account_code,2) = 'FY' then '-'
when isNumeric(left(account_code,1)) =1 then substring(account_code,2,1)
when isNumeric(left(account_code,1))=0 and left(account_code,2) <> 'FY'
then left(account_code, 1)
end as function,
--set capital depending on account_code (able to set dep. on function?)
case
when isNumeric(left(account_code,1)) =1 then tran_amount
else 0
end as capital,
--set revenue depending on account_code (able to set dep. on function?)
case
when isNumeric(left(account_code,1)) =0 and left(account_code,2) <> 'FY' then tran_amount
else 0
end as revenue,
--set misc depending on account_code (able to set dep. on function?)
case
when left(account_code,2) = 'FY' then tran_amount
else 0
end as misc,
'INVOICE'
from
tblLoadMSV900_i

insert into [tblInvoices]
(full_period,
account_code,
tran_amount,
function,
capital,
revenue,
misc,
source)
select
full_period,
account_code,
tran_amount,
--set function depending on account_code
case
when left(account_code,2) = 'FY' then '-'
when isNumeric(left(account_code,1)) =1 then substring(account_code,2,1)
when isNumeric(left(account_code,1))=0 and left(account_code,2) <> 'FY'
then left(account_code, 1)
end as function,
--set capital depending on account_code (able to set dep. on function?)
case
when isNumeric(left(account_code,1)) =1 then tran_amount
else 0
end as capital,
--set revenue depending on account_code (able to set dep. on function?)
case
when isNumeric(left(account_code,1)) =0 and left(account_code,2) <> 'FY' then tran_amount
else 0
end as revenue,
--set misc depending on account_code (able to set dep. on function?)
case
when left(account_code,2) = 'FY' then tran_amount
else 0
end as misc,
'STOCK'
from
tblLoadMSV900_r

Only way I see is to use a UNION to combine both selects into one insert statement

If you just want to simply the code you could create a view doing the select from both source tables and then do your insert as:

INSERT INTO [tblInvoices]
(full_period,
account_code,
tran_amount,
function,
capital,
revenue,
misc,
source)
SELECT
full_period,
account_code,
tran_amount,
function,
capital,
revenue,
misc,
source
FROM yourView