locked
Insert Records from Multiple Tables RRS feed

  • Question

  • This is a bit lengthy, but lets say we have three tables

    a) tblSaleStatementCustomer
    b) tblCreditors
    c) tblReceiptDue

    which shows records like below

    Table 1 - tblSaleStatementCustomer

    ID   CustomerName     VoucherType    Outbound     Inbound     CustomerType
    ----------------------------------------------------------------------------------------------
    1     ABC                              Sales                10000              0                   Dealer
    2     MNC                             Sales                  9000              0                   Dealer
    3     MNC                             Sales                  4000              0                   Dealer

    Table 2 -  tblCreditors

    ID   Name     OpeningBalance
    ----------------------------------------------------------------------------------------------
    1     ABC          20000  
    2     MNC         15000 
    3     XBM         18000
    4     XYZ          12000

    Table 3 -  tblReceiptDue ( this table is blank, just have three columns)

    ID   Dealer     Amount
    ----------------------------------------------------------------------------------------------

    Now, I am executing the below query

    WITH cte AS (SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END) AS AMOUNT from tblSaleStatementCustomer  WHERE CustomerType = 'Dealer' GROUP BY CustomerName) INSERT INTO tblReceiptDue (Dealer, Amount) SELECT CustomerName ,SUM(AMOUNT+OpeningBalance) as AMOUNT FROM cte JOIN tblCreditors ON cte.CustomerName=tblCreditors.Name GROUP BY CustomerName

    Actually, the above query looks for Dealers in CustomerName  column of tblSaleStatement that also exist in the Name columns of tblCreditors, and then sum up  Outbound Column of tblSaleStatement with the OpeningBalance column of tblCreditors. So we get the result similar to below

    Table 3 -  tblReceiptDue ( data inserted after the query)

    ID   Dealer     Amount
    ----------------------------------------------------------------------------------------------
    1     ABC         30000
    2     MNC        28000

    But the problem is that, the data generated after the query only shows those dealers that were found in both the tables (tblSaleStatementCustomer and tblCreditors respectively). I want that the result also include those dealers as well those are listed in tblCreditors, doesn't matter if they exist in tblSaleCustomer or not, so we should get the result similar to below.

    ID   Dealer     Amount
    ----------------------------------------------------------------------------------------------
    1     ABC         30000
    2     MNC        28000
    3     XBM         18000
    4     XYZ          12000

    Can somebody help me out please.

    Sunday, August 30, 2015 10:37 PM

Answers

  • The following should only be used if you do NOT have multiple rows in tblCreditors with the same Name.

    ;WITH cte AS (SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END) AS AMOUNT 
    from tblSaleStatementCustomer  
    WHERE CustomerType = 'Dealer' 
    GROUP BY CustomerName) 
    INSERT INTO tblReceiptDue (Dealer, Amount) 
    SELECT tblCreditors.Name, ISNULL(AMOUNT, 0) + OpeningBalance as AMOUNT 
    FROM tblCreditors
    LEFT JOIN cte ON cte.CustomerName=tblCreditors.Name

    If you have (or might have) multiple rows in tblCreditors with the same Name, then in order to get the correct query, you will have to tell us what result you want in that case.

    Tom


    • Edited by Tom Cooper Sunday, August 30, 2015 11:05 PM
    • Proposed as answer by Naomi N Monday, August 31, 2015 1:11 AM
    • Marked as answer by Sid Williams Monday, August 31, 2015 5:36 AM
    Sunday, August 30, 2015 11:05 PM

All replies

  • The following should only be used if you do NOT have multiple rows in tblCreditors with the same Name.

    ;WITH cte AS (SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END) AS AMOUNT 
    from tblSaleStatementCustomer  
    WHERE CustomerType = 'Dealer' 
    GROUP BY CustomerName) 
    INSERT INTO tblReceiptDue (Dealer, Amount) 
    SELECT tblCreditors.Name, ISNULL(AMOUNT, 0) + OpeningBalance as AMOUNT 
    FROM tblCreditors
    LEFT JOIN cte ON cte.CustomerName=tblCreditors.Name

    If you have (or might have) multiple rows in tblCreditors with the same Name, then in order to get the correct query, you will have to tell us what result you want in that case.

    Tom


    • Edited by Tom Cooper Sunday, August 30, 2015 11:05 PM
    • Proposed as answer by Naomi N Monday, August 31, 2015 1:11 AM
    • Marked as answer by Sid Williams Monday, August 31, 2015 5:36 AM
    Sunday, August 30, 2015 11:05 PM
  • Try

    ;WITH cte AS (SELECT CustomerName, CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END AS AMOUNT 
    from tblSaleStatementCustomer  
    WHERE CustomerType = 'Dealer' 
    ) 
    INSERT INTO tblReceiptDue (Dealer, Amount) 
    SELECT tblCreditors.Name, SUM(ISNULL(AMOUNT, 0) + OpeningBalance) as AMOUNT 
    FROM tblCreditors
    LEFT JOIN cte ON cte.CustomerName=tblCreditors.Name
    GROUP BY tblCreditors.Name


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, August 31, 2015 1:15 AM