tsql to JOIN the results of 2 temp tables (1 Viewer)

Mittle

Member
Local time
Today, 01:51
Joined
Dec 2, 2020
Messages
105
Hello
Please need help with Part 2 . how to join data from temp table

am trying to create a simple stored .

1. that dumps the results of 2 view into 2 different temp table . this works fine


SELECT * INTO #temptable FROM vwdata1
SELECT * INTO #Temptable2 FROM vwdata2

2. Run my final query by Joining the data from these 2 views in the same stored proc.this doesnt work

SELECT * FROM #temptable
INNER JOIN

SELECT * FROM #Temptable2
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:51
Joined
May 7, 2009
Messages
19,169
use Union Query?
 

Mittle

Member
Local time
Today, 01:51
Joined
Dec 2, 2020
Messages
105
use Union Query?
sorry I forgot to mention that the columns are not similar in the separate views . so view A has Columns 1-8 and ViewB has columns 9-15. so UNION wont work
 

Minty

AWF VIP
Local time
Today, 01:51
Joined
Jul 26, 2013
Messages
10,355
You need to specify a joining condition, unless you want a cartiesian join ?

So either something like

Code:
SELECT * FROM #temptable
CROSS JOIN
 #Temptable2

Or

Code:
SELECT * FROM #temptable
INNER JOIN
#Temptable2
On Field_X.#temptable = Field_Y.#Temptable2

It could of course be a LEFT join instead
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:51
Joined
May 7, 2009
Messages
19,169
use Cartesian Query?

select table1.*, table2.* from table1, table2;

Union will work if you "manually" specify the columns.
 

Mittle

Member
Local time
Today, 01:51
Joined
Dec 2, 2020
Messages
105
am joining columns , so

View 1 has Column1-8
View 2 has Column9-12.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:51
Joined
May 7, 2009
Messages
19,169
select field1, field2, field3, field4, field5, field6, field7, field8, null, null, null, null from View1
union
select field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12 from View2;
 

Minty

AWF VIP
Local time
Today, 01:51
Joined
Jul 26, 2013
Messages
10,355
You were using this join when using the views - so it should be the same, just substitute the #TempTable names from the views?

INNER JOIN [vwHeadline Data pt2] ON [vwHeadline Data pt1].ProjReturnID = [vwHeadline Data pt2].ProjReturnID)
LEFT JOIN vwScopeChangeGroup ON [vwHeadline Data pt1].ProjReturnID = vwScopeChangeGroup.ProjectReturnID;

But this looks like there are actually 3 views involved not just 2?
 

Mittle

Member
Local time
Today, 01:51
Joined
Dec 2, 2020
Messages
105
yes 3 views and ive added it. Fantastic . this works Perfect .This Forum is the best that you get experts on a Subject matter and to top it up they reply almost instantly to issues from other people. I just happened to stumble on this website and ever since its been the most useful .
 

Minty

AWF VIP
Local time
Today, 01:51
Joined
Jul 26, 2013
Messages
10,355
Excellent - and how long dos it take to return the results?
 

Mittle

Member
Local time
Today, 01:51
Joined
Dec 2, 2020
Messages
105
Yes Apologies , was just coming to that .it took about 2 seconds to run the joins between the 3 views as you said .

I now need to tidy up the stored proc and exec the stored proc . not that it will so much difference. will let you know
 
Last edited:

Mittle

Member
Local time
Today, 01:51
Joined
Dec 2, 2020
Messages
105
Yes Apologies , was just coming to that .it took about 2 seconds to run as you said .

I now need to tidy up the stored proc and exec the stored proc . not that it will make much difference
Also just a quick question . sorry might sound dumb but as to why so much difference with using a temp table .

I know the theory about the use of temp tables as workspaces for storing intermediate results etc.
 

Minty

AWF VIP
Local time
Today, 01:51
Joined
Jul 26, 2013
Messages
10,355
You are only running each query once and storing the result set into the temptables.
You then perform the single end query to join those results sets together.

With your original method you were effectively running query1 say 50 times and within that query2 4000 times ( have made up the numbers but you see the effect) so 80000 queries (50*4000) all taking 2-3 seconds adds up to a long wait or a crash.

This is one reason why views based on views can get dangerous. It's a common practice in Access as it doesn't handle things in the same way but the net result is often the same - ponderously slow results, as you are effectively performing row by row processing albeit in a set based starting position. This is why you should avoid DLookups in queries in Access

By using temp tables you are moving the whole operation into a nice fixed set-based query.
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:51
Joined
Mar 14, 2017
Messages
8,738
SELECT * FROM #temptable
INNER JOIN

SELECT * FROM #Temptable2
That's not correct. You want something more like:

Code:
select
    *
from
    #temptable tt
    inner join #temptable2 tt2 on tt2.ID = tt.ID
 

Mittle

Member
Local time
Today, 01:51
Joined
Dec 2, 2020
Messages
105
slight thing just found out .


because the 3 different views have this same 3 columns

ProjectReturnID
QTRID
ProjectID

I now have the results showing these Columns 3 times .

how do I limit to just give me one instance of these columns
 

Mittle

Member
Local time
Today, 01:51
Joined
Dec 2, 2020
Messages
105
That's not correct. You want something more like:

Code:
select
    *
from
    #temptable tt
    inner join #temptable2 tt2 on tt2.ID = tt.ID
Oh thanks Isaac that bit of the code has now been resolved by Minty
 

Minty

AWF VIP
Local time
Today, 01:51
Joined
Jul 26, 2013
Messages
10,355
You would have to specify the fields you want in the final select query in the SP, rather than using SELECT *
 

Mittle

Member
Local time
Today, 01:51
Joined
Dec 2, 2020
Messages
105
ok thanks will try that , No shortcut for this one I suppose.
 

Minty

AWF VIP
Local time
Today, 01:51
Joined
Jul 26, 2013
Messages
10,355
ok thanks will try that , No shortcut for this one I suppose.

You can alte the SP to list the fields in the Temp table and then cut and paste them back into the SP as a cheat...
 

Users who are viewing this thread

Top Bottom