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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

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

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

                      Resources.ResourcesID, Resources.Cash, Resources.Savings, Resources.CreditFacilities, Resources.OverdraftFacilities, 
                      Resources.AssurancePolicy, Resources.PremiumBonds, Resources.Shares, Resources.Other, Resources.OtherProperty, 
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

                      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."

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

Flowing Fount of Yak Knowledge

2875 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


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

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  
 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.03 seconds. Powered By: Snitz Forums 2000