-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|