Post reply

Insert into with Multiple select statements

  • October 23, 2013 at 1:46 am

    #278357

    Hi All,

    I have a table with 10 number of columns, out of 10 five need to get the data from different tables.Say TB1 has 10 columns, the 5 columns need to be populated from a single source table the other five has to be populated from its associated columns from 5 different tables. I have written some thing like this but it's not working

    Insert into Daily.QRM_OffBalancesheet(

    TCMTradeID,BusinessDate,SDL,TCMProductID,TCMOrganisationID,ExposureAmount,ExposureCurrencyID,

    TradeDate,MaturityDate,TCMLegalEntityID)

    select TransactionID,BusinessDate,SourceSystemName,

    (select reference.ProductDescription

    from ODS.Daily.TCMStdTradeClaimJHB as Undrawn

    Left join ODS.reference.TCMRefProduct as Reference on

    Undrawn.TCMProductID = Reference.TCMProductID

    where Undrawn.TCMProductID in (102,196,202)),BusinessUnit,Balance,

    (select Reference.currencyISOCode

    from ODS.Daily.TCMStdTradeClaimJHB as Undrawn

    Left Join ODS.Reference.TCMRefCurrency as Reference On

    Undrawn.ExposureCurrencyID = Reference.TCMCurrencyID

    WHERE Undrawn.TCMProductID IN (102,196,202)),StartDate,MaturityDate,

    (select Reference.legalEntityCode

    from ODS.Daily.TCMStdTradeclaimJHB as TradesClaim

    Left Join ODS.Reference.TCMRefGroupLegalEntity as Reference

    ON TradesClaim.TCMLegalEntityID = Reference.TCMLegalEntityID)

    from Daily.TCMStdTradeClaimJHB

    It's pretty urgent.. your help is much appreciated 🙂

  • October 23, 2013 at 3:01 am

    #1660825

    Does any one has inputs on this ?

  • October 23, 2013 at 3:22 am

    #1660831

    I'd suggest you read up on how to join tables. You are referencing tables in your correlated subqueries who are aliased in another correlated subquery. This is not possible.

    Example: in the first subquery, you cannot use the table Reference.

    Correlated are also a big no-no: they are very bad for performance.

    edit: if you want people to help you with actually writing a query, you'd want to post the table DDLs and some sample data.

    edit part 2: I rechecked the query in SSMS with some syntax highlighting to make it more readable. I was mistaken: you are not using correlated subqueries but just regular subqueries. Each subquery has Undrawn and Reference table, something I missed in my earlier reply. However, each subquery probably returns more than one row, which is not allowed.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 23, 2013 at 4:24 am

    #1660846

    Thanks for your Inputs. I did managed to join the tables.

  • October 23, 2013 at 4:44 am

    #1660853

    Suresh Babu Palla (10/23/2013)

    Thanks for your Inputs. I did managed to join the tables.

    Did you get rid of the subqueries?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 23, 2013 at 10:06 am

    #1661011

    Yes, I managed to getrid of subqueries and joined all my selects in to one and got the result.

  • October 23, 2013 at 10:58 am

    #1661039

    Suresh Babu Palla (10/23/2013)

    Yes, I managed to getrid of subqueries and joined all my selects in to one and got the result.

    Great, glad you got it worked out.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply