2 temp tables into one - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

2 temp tables into one

Author

Topic

Tones
Starting Member

17 Posts

Posted - 2012-11-27 : 10:36:54

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.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-27 : 10:48:18

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

Tones
Starting Member

17 Posts

Posted - 2012-11-27 : 11:21:21

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources