Results 1 to 2 of 2

Thread: Inserting data from multiple tables

  1. #1
    Join Date
    Mar 2003
    Posts
    7

    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

  2. #2
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    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
  •