# Miscellaneous > Structured Query Language (SQL) >  Inserting data from multiple tables

## sara

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

----------


## andi_g69

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

----------

