SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 2 temp tables into one
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tones
Starting Member

United Kingdom
17 Posts

Posted - 11/27/2012 :  10:36:54  Show Profile  Reply with Quote
I am running the code below to get 2 temp tables, #applicantResources and #partnerResources. The fields are identical but aliased. I now need to return the 2 sets of results into one row in a single table, to be used for populating a formview in asp.net

INSERT INTO #ApplicantResources
(
ResourcesID, Cash, Savings, CreditFacilities, OverdraftFacilities, AssurancePolicy, PremiumBonds, Shares, Other, OtherProperty, OtherSource
)

SELECT DISTINCT
                      Resources.ResourcesID, Resources.Cash, Resources.Savings, Resources.CreditFacilities, Resources.OverdraftFacilities, 
                      Resources.AssurancePolicy, Resources.PremiumBonds, Resources.Shares, Resources.Other, Resources.OtherProperty, 
                      Resources.OtherSource
FROM         Resources INNER JOIN
                      ApplicationFinancialDetails ON Resources.ApplicationFinancialDetailsID = ApplicationFinancialDetails.ApplicationFinancialDetailsID INNER JOIN
                      CustomerApplication ON ApplicationFinancialDetails.CustomerID = CustomerApplication.CustomerID
WHERE     (Resources.ApplicationFinancialDetailsID = 96
          

INSERT INTO #PartnerResources
(
PartnerResourcesID, PartnerCash, PartnerSavings, PartnerCreditFacilities, PartnerOverdraftFacilities, PartnerAssurancePolicy, PartnerPremiumBonds, PartnerShares, PartnerOther, PartnerOtherProperty, PartnerOtherSourcebit
)

SELECT DISTINCT
                      PartnerResources.ResourcesID as PartnerResourcesID, PartnerResources.Cash as PartnerCash, PartnerResources.Savings as PartnerSavings, 
                      PartnerResources.CreditFacilities as PartnerCreditFacilities, PartnerResources.OverdraftFacilities as PartnerOverdraftFacilities, 
                      PartnerResources.AssurancePolicy as PartnerAssurancePolicy, PartnerResources.PremiumBonds as PartnerPremiumBonds, PartnerResources.Shares as PartnerShares, 
                      PartnerResources.Other as PartnerOther, PartnerResources.OtherProperty as PartnerOtherProperty, 
                      PartnerResources.OtherSource as PartnerOtherSource
FROM         Resources as PartnerResources INNER JOIN
                      ApplicationFinancialDetails ON PartnerResources.ApplicationFinancialDetailsID = ApplicationFinancialDetails.ApplicationFinancialDetailsID INNER JOIN
                      CustomerApplication ON ApplicationFinancialDetails.CustomerID = CustomerApplication.CustomerID
WHERE     (PartnerResources.ApplicationFinancialDetailsID = 96


I tried doing a UNION to get the results into one table but get error "Column name or number of supplied values does not match table definition."

CREATE TABLE #tempTable
(
ResourcesID int,
Cash decimal(18,2),
Savings decimal(18,2),
CreditFacilities decimal(18,2),
OverdraftFacilities decimal(18,2),
AssurancePolicy decimal(18,2),
PremiumBonds decimal(18,2),
Shares decimal(18,2),
Other decimal(18,2),
OtherProperty bit,
OtherSource bit,
PartnerResourcesID int,
PartnerCash decimal(18,2),
PartnerSavings decimal(18,2),
PartnerCreditFacilities decimal(18,2),
PartnerOverdraftFacilities decimal(18,2),
PartnerAssurancePolicy decimal(18,2),
PartnerPremiumBonds decimal(18,2),
PartnerShares decimal(18,2),
PartnerOther decimal(18,2),
PartnerOtherProperty bit,
PartnerOtherSource bit
)

insert into #tempTable

select * from #ApplicantResources
union all
select * from #PartnerResources

SELECT * FROM #tempTable


i may not be doing this right. I have been able to get results from both tables into one table but in 2 rows. I need them on one row with the different column names.

Edited by - Tones on 11/27/2012 10:46:56

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/27/2012 :  10:48:18  Show Profile  Reply with Quote
That's not actually a union that you want, but a join. Something like

select ar.<All the columns you want> , pr.<all the columns you want>
from #ApplicantResources ar
full join #PartnerResources pr
on ar.ResourcesId = pr.resourcesID



Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Tones
Starting Member

United Kingdom
17 Posts

Posted - 11/27/2012 :  11:21:21  Show Profile  Reply with Quote
Yes thats more what im looking for. I thought i couldnt do the join as the resourcesID was different in both temp tables. Fixed by creating a dummy id record in both temp tables and joining on that.

Thanks jim
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.42 seconds. Powered By: Snitz Forums 2000