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

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
Getting stuck with my Stored proc. Can anyone please help spot what am doing wrong here .

I am getting 2 sets of results but only want 1 set which is the end result ( Selecting the required columns) in Step 3.

1. Step 2 results
2.Step3 results


Use dbname
GO

CREATE PROC [dbo].[usp_ViewData]
AS


BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

---1.drop temp tables if it exist

IF OBJECT_ID('tempdb.dbo.#myTempTable', 'U') IS NOT NULL
DROP TABLE #MyTempTable;

IF OBJECT_ID('tempdb.dbo.#myTempTable2', 'U') IS NOT NULL
DROP TABLE #MyTempTable2;


IF OBJECT_ID('tempdb.dbo.#ScopeTemptable', 'U') IS NOT NULL
DROP TABLE #ScopeTemptable;

--2-- Creates temp table by selecting into temp table

SELECT * INTO #MyTempTable FROM [dbo].[vw1 Data pt1]

SELECT * INTO #MyTempTable2 FROM [dbo].[vw2 Data pt2]

SELECT * INTO #ScopeTemptable FROM [dbo].[vw3 Scope]

------ Run the query for to join the results from the 3 views

SELECT * FROM #MyTempTable
INNER JOIN #MyTempTable2
ON #MyTempTable.ProjReturnID = #MyTempTable2.ProjReturnID
LEFT JOIN #ScopeTemptable ON #MyTempTable.ProjReturnID = #ScopeTemptable.ProjectReturnID

----------------------------------------------------------------------------------------------
--- 3. SELECT required data from the 3 #temptables
SELECT
t1.IDQ,
t1.[ID Number],
t1.ProjReturnID,
t2.[Benefits - Net Provided],
SC.CountOfProjScopeChngNo,
t2.[Start Date - Variance (Days)],
t2.[EndDate - Variance (Days)],
t2.[End Date - Variance (%)]
FROM #MyTempTable t1
INNER JOIN #MyTempTable2 t2
ON t1.ProjReturnID = t2.ProjReturnID
LEFT JOIN ScopeTemtable Sc ON t1.ProjReturnID = Sc.ProjectReturnID



---4. Drop temp tables

DROP TABLE #MyTempTable;
DROP TABLE #ScopeTemptable;
DROP TABLE #MyTempTable2


END
 
Last edited:

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,355
You can't do that, you aren't joining the tables in query 3.
Something like this might point you in the right direction, and please use code tags (It's the </> icon above the reply editor)
SQL:
    SELECT
        t1.IDQ,
        t1.[ID Number],
        t1.ProjReturnID,
        t2.[Benefits - Net Provided],
        SC.CountOfProjScopeChngNo,
        t2.[Start Date - Variance (Days)],
        t2.[EndDate - Variance (Days)],
        t2.[End Date - Variance (%)]
        FROM
        (
        SELECT * FROM #MyTempTable t1
        INNER JOIN #MyTempTable2 t2
        ON t1.ProjReturnID = t2.ProjReturnID
        LEFT JOIN #ScopeTemptable SC ON t1.ProjReturnID = SC.ProjectReturnID
        ) as x
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
Yes ur very correct .Apologies I've corrected the code earlier this morning
But still having issues with 2 sets of result
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
You can't do that, you aren't joining the tables in query 3.
Something like this might point you in the right direction, and please use code tags (It's the </> icon above the reply editor)
SQL:
    SELECT
        t1.IDQ,
        t1.[ID Number],
        t1.ProjReturnID,
        t2.[Benefits - Net Provided],
        SC.CountOfProjScopeChngNo,
        t2.[Start Date - Variance (Days)],
        t2.[EndDate - Variance (Days)],
        t2.[End Date - Variance (%)]
        FROM
        (
        SELECT * FROM #MyTempTable t1
        INNER JOIN #MyTempTable2 t2
        ON t1.ProjReturnID = t2.ProjReturnID
        LEFT JOIN #ScopeTemptable SC ON t1.ProjReturnID = SC.ProjectReturnID
        ) as x
J
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
1.there were 2 issues and yes I corrected one as I wasn't joining the tables .I have now use the correct joins and aliases in Step 3


2.results appearing in 2 parts--------------oh I see what your saying .I need to put in a subquery .

thanks will try that

3. ok will use the code tags next time
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
Thanks so much .now getting one Minty for your help in resolving my issues .it turns out I dont need the subquery because I was getting the following errors below

the column ProjectID was specified multiple times for A.
 

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,355
I'm not sure I understand if you have fixed it or not.

Can you post up the code if you have a problem?
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
yes my last post was confusing .

the code to use the subquery is giving errors about . didn't seem to line the as X alias too

Member​

Local timeToday, 10:57JoinedDec 2, 2020Messages36

the column ProjectID was specified multiple times for A.
Multpart identifier t1.IDQ could not be bound
Multpart identifier t1.IDNumber could not be bound







I have removed the subquery and now used which works


Code:
   SELECT
        t1.IDQ,
        t1.[ID Number],
        t1.ProjReturnID,
        t2.[Benefits - Net Provided],
        SC.CountOfProjScopeChngNo,
        t2.[Start Date - Variance (Days)],
        t2.[EndDate - Variance (Days)],
        t2.[End Date - Variance (%)]
        FROM
        
         #MyTempTable t1
        INNER JOIN #MyTempTable2 t2
        ON t1.ProjReturnID = t2.ProjReturnID
        LEFT JOIN #ScopeTemptable SC ON t1.ProjReturnID = SC.ProjectReturnID
 

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,355
Yes - that looks good.
I confused myself trying to cut and paste when I should have simply written what I thought... 😁

As you have discovered the SP will return any SELECT statements as a result set.
Great for testing, not so great if you forget and return the whole lot to Access 🤦‍♂️
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
Yes - that looks good.
I confused myself trying to cut and paste when I should have simply written what I thought... 😁

As you have discovered the SP will return any SELECT statements as a result set.
Great for testing, not so great if you forget and return the whole lot to Access 🤦‍♂️
thanks so much,ve learnt so much but what was the problem with the subquery though.
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
Also just wanted your general thought on the View I sent previously with the Calculated fields - the one that runs for 6 secs.

would you have created it on its own as a stored proc . do u think we could get any performance gains .

I have created most of the access queries as views in sql . except when it came to these 2 Views B and C and as you rightly advised to turn them into stored proc and dump the views into temp tables which is Perfect .
 

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,355
Generally speaking, Views are a good thing, you should only really head down the SP route when a straightforward query won't do enough, or there are performance issues, or if you need to pass in parameters to control an output, views can't take parameters.

Always check the performance of your view by running it as a simple query with the execution plan turned on, it will guide you if any indexes might help performance.

Views with calculated fields are definitely a good way to remove the heavy lifting from Access.
I generally don't use views very often as a source in an SP, but again there are situations where it makes sense.

We have one large application where there are about 50 calculated columns required for a variety of outputs.
We chucked the whole lot into one view and selectively run a variety of report generating SP's off that one view.
It guarantees consistency across all out reports, and if the client changes a calculation we update the view accordingly and all the reports values will follow the updated calculation.
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
Fantastic. thats all I needed . Thanks ever so much
 

Isaac

Lifelong Learner
Local time
Today, 04:03
Joined
Mar 14, 2017
Messages
8,738
Generally speaking, Views are a good thing, you should only really head down the SP route when a straightforward query won't do enough, or there are performance issues, or if you need to pass in parameters to control an output, views can't take parameters.

Always check the performance of your view by running it as a simple query with the execution plan turned on, it will guide you if any indexes might help performance.

Views with calculated fields are definitely a good way to remove the heavy lifting from Access.
I generally don't use views very often as a source in an SP, but again there are situations where it makes sense.

We have one large application where there are about 50 calculated columns required for a variety of outputs.
We chucked the whole lot into one view and selectively run a variety of report generating SP's off that one view.
It guarantees consistency across all out reports, and if the client changes a calculation we update the view accordingly and all the reports values will follow the updated calculation.
IMO:
Views are good but are typically over-used. Once done in any appreciable volume, you will necessarily end up with nested views. Major performance killers.
SP's are the way to encapsulate pretty much all action going on.

Match them together with table-valued functions, and you'll eliminate 90% of both.
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
I think it depends . in this case the data is mostly for reporting so think Views are good. if I wrap all my SELECT in a Stored procedure, what benefit will it give me if am not passing in parameters etc, not sure why I would use an SP in that situation .
 

Isaac

Lifelong Learner
Local time
Today, 04:03
Joined
Mar 14, 2017
Messages
8,738
That's why I said SP's are for action. (update, insert, delete).
However, there are a number of other creative things that can't be done in Views, either.

I agree, it all depends. One thing I don't end up with is a lot of random saved .sql files, unless they are 1) things still under dev, 2) production fixes/one-time things
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
Oh Yes you are very correct .
Views are just mostly for the straightforward SELECTS statements
I wish am an expert at writing stored procedures though as I come from the DBA background
Hoping to get there one day .
Thanks for your comments and help so far
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
yes my last post was confusing .

the code to use the subquery is giving errors about . didn't seem to line the as X alias too

Member​

Local timeToday, 10:57JoinedDec 2, 2020Messages36

the column ProjectID was specified multiple times for A.
Multpart identifier t1.IDQ could not be bound
Multpart identifier t1.IDNumber could not be bound







I have removed the subquery and now used which works


Code:
   SELECT
        t1.IDQ,
        t1.[ID Number],
        t1.ProjReturnID,
        t2.[Benefits - Net Provided],
        SC.CountOfProjScopeChngNo,
        t2.[Start Date - Variance (Days)],
        t2.[EndDate - Variance (Days)],
        t2.[End Date - Variance (%)]
        FROM
       
         #MyTempTable t1
        INNER JOIN #MyTempTable2 t2
        ON t1.ProjReturnID = t2.ProjReturnID
        LEFT JOIN #ScopeTemptable SC ON t1.ProjReturnID = SC.ProjectReturnID
Hi All

does anyone have any idea how to turn this stored procedure into a table please i.e would like the columns produced by this stored proc made into a permanent table in SQL Server .

if you recall ,the reason this stored proc was required in the first place is to join 3 views together using temp tables to alleviate the performance problems I had with the slow running views

is this possible ?



Thanks
 

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,355
Yes it's certainly possible.

I'm guessing you would want to delete the table contents and replace with new each time it is run?

So your SP would be something like
SQL:
    -- Stuff to create your #temptable goes here
   
    -- This assumes you have already created the new table YourNewTable
   
    TRUNCATE TABLE YourNewTable -- Clear the existing data, This is much quicker than a delete
   
    INSERT INTO YourNewTable

        SELECT
            t1.IDQ,
            t1.[ID Number],
            t1.ProjReturnID,
            t2.[Benefits - Net Provided],
            SC.CountOfProjScopeChngNo,
            t2.[Start Date - Variance (Days)],
            t2.[EndDate - Variance (Days)],
            t2.[End Date - Variance (%)]
            FROM

             #MyTempTable t1
            INNER JOIN #MyTempTable2 t2
            ON t1.ProjReturnID = t2.ProjReturnID
            LEFT JOIN #ScopeTemptable SC ON t1.ProjReturnID = SC.ProjectReturnID

Doing it this way means you can create indexes on the new table that will stay put.
You could simply drop the table and recreate but this is a more elegant method IMHO.
 

Mittle

Member
Local time
Today, 11:03
Joined
Dec 2, 2020
Messages
105
Yes it's certainly possible.

I'm guessing you would want to delete the table contents and replace with new each time it is run?

So your SP would be something like
SQL:
    -- Stuff to create your #temptable goes here

    -- This assumes you have already created the new table YourNewTable

    TRUNCATE TABLE YourNewTable -- Clear the existing data, This is much quicker than a delete

    INSERT INTO YourNewTable

        SELECT
            t1.IDQ,
            t1.[ID Number],
            t1.ProjReturnID,
            t2.[Benefits - Net Provided],
            SC.CountOfProjScopeChngNo,
            t2.[Start Date - Variance (Days)],
            t2.[EndDate - Variance (Days)],
            t2.[End Date - Variance (%)]
            FROM

             #MyTempTable t1
            INNER JOIN #MyTempTable2 t2
            ON t1.ProjReturnID = t2.ProjReturnID
            LEFT JOIN #ScopeTemptable SC ON t1.ProjReturnID = SC.ProjectReturnID

Doing it this way means you can create indexes on the new table that will stay put.
You could simply drop the table and recreate but this is a more elegant method IMHO.
thanks Minty. if you remember this is the whole code to create the sp here below the reason I need a permanent table is for users accessing via tableau so they can access the table and select columns as they want rather than executing the stored proc

anyway the answer is NO I haven't created a table yet?

so I need to create table ? is it best to create table in this sp or just create the permanent table separately and datatypes etc?
please note that this report has over 200 columns so I would need a table with that no of columns


just pondering on this request from users , this stored proc is meant to produce a report which I now need to turn into a permanent table . is this how reports should work?








Code:
Use dbname
GO

CREATE PROC [dbo].[usp_ViewData]
AS


BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

---1.drop temp tables if it exist

IF OBJECT_ID('tempdb.dbo.#myTempTable', 'U') IS NOT NULL
DROP TABLE #MyTempTable;

IF OBJECT_ID('tempdb.dbo.#myTempTable2', 'U') IS NOT NULL
DROP TABLE #MyTempTable2;


IF OBJECT_ID('tempdb.dbo.#ScopeTemptable', 'U') IS NOT NULL
DROP TABLE #ScopeTemptable;

--2-- Creates temp table by selecting into temp table

SELECT * INTO #MyTempTable FROM [dbo].[vw1 Data pt1]

SELECT * INTO #MyTempTable2 FROM [dbo].[vw2 Data pt2]

SELECT * INTO #ScopeTemptable FROM [dbo].[vw3 Scope]

------ Run the query for to join the results from the 3 views

SELECT * FROM #MyTempTable
INNER JOIN #MyTempTable2
ON #MyTempTable.ProjReturnID = #MyTempTable2.ProjReturnID
LEFT JOIN #ScopeTemptable ON #MyTempTable.ProjReturnID = #ScopeTemptable.ProjectReturnID

----------------------------------------------------------------------------------------------
--- 3. SELECT required data from the 3 #temptables
SELECT
t1.IDQ,
t1.[ID Number],
t1.ProjReturnID,
t2.[Benefits - Net Provided],
SC.CountOfProjScopeChngNo,
t2.[Start Date - Variance (Days)],
t2.[EndDate - Variance (Days)],
t2.[End Date - Variance (%)]
FROM #MyTempTable t1
INNER JOIN #MyTempTable2 t2
ON t1.ProjReturnID = t2.ProjReturnID
LEFT JOIN ScopeTemtable Sc ON t1.ProjReturnID = Sc.ProjectReturnID



---4. Drop temp tables

DROP TABLE #MyTempTable;
DROP TABLE #ScopeTemptable;
DROP TABLE #MyTempTable2


END
 
Last edited:

Users who are viewing this thread

Top Bottom