Insert into with Multiple select statements
-
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
-
Does any one has inputs on this ?
-
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 -
Thanks for your Inputs. I did managed to join the tables.
-
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 -
Yes, I managed to getrid of subqueries and joined all my selects in to one and got the result.
-
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