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.
2 temp tables into one
Posted - 11/27/2012 : 10:36:54
