0

Within my sql procedure I create an temporary table and then pivot it. This is dynamic so in the end I don't know how many columns there are. Lets make this table:

equipmentID   |   Name1   |   Name2   | Name3
1             |   34.54   |   21.23   | 213.65
2             |   334     |   23.421  | 23.12
3             |   43.4    |   2.34    | 23.41

The query used to generate this is stored in a string and is executing by calling execute(@stringQuery).

My other table is fetched with a normal sql query and generates another set of information (including the equipmentID that is included in the pivot table.

What I want to do is join these two tables (based on the equipmentID so that the stored procedure returns a single table instead of two but I can't figure out how to do this.

So far I have tried several things:

  • Keep the main (second) query and add the execute line to in at the end of the FROM section so after all the tables are joined I have:

    LEFT JOIN execute(@stringQuery) as strQ on strQ.equipmentID = mainQ.equipmentID

  • Made the second query a string so they are both fetched with the execute command OR created another stored procedure that contained the main sql so (again) they both use the execute command. This does fetch the information it just doesn't combine it. In this case I tried to combine these two executes with:

    execute('select * from (' + @query1 + ') as tbl2 left join ' + @query2 + ' as tbl1 on tbl2.equipmentID= tbl1.equipmentID')

  • Convert the main query into a string and then combined them using a LEFT JOIN so it becomes one big query.

All of these gave me issues. I figured this was a common thing and wasn't sure how to approach solving it other than with what I tried already.

EDIT When I try to create a temporary table and the insert into (or select from) I get told the temporary table name is an invalid object.

Improve this question

3 Answers 3

Reset to default
1

How about have the dynamic query output to a temp table. Then you can just reference the output like any other table.

Dynamic Query:

SELECT x,y,z 
INTO #MyTempTable
...

Final Query:

SELECT *
FROM TABLE
    JOIN #MyTempTable ON ...
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks. I don't know why I didn't see that. I was making it so complicated lol I will try this when I go back to work :D
Actually I remember I already tried this but because the number of columns resulting from the pivot is dynamic I had to make the create table in a string and then execute it. Then whenever I tried to execute the procedure so it would be in the DB it wouldn't run as the table doesn't exist.
@Kyra Huh? This should work. The temp table should be built using the same session and so the table should be usable. Is this all happening in the same session?
Its happening happening in the same transaction. I can try this at work Monday again. :)
Note that when you use "SELECT INTO" you don't need to have explicitly created the table first. This is exactly how I've done similar queries. The alternative is creating global temp tables ## etc, but then you need to dynamically create the name to not overlap (with GUID() for example). Not recommended as this approach is simpler.
1
+50

Your question is somewhat harder than it may seem. The trouble stems from the fact that you have a table with an unknown format (the dynamically generated columns).

The table with columns that only become known at runtime forces a solution that is either complex and cumbersome, or a solution that is not ideal.

The global temp table solution you have chosen may be one of the simplest solutions, but have a look here (skip down to "Global Temp Tables" or search for "##") for some pitfalls with your chosen solution: http://www.sommarskog.se/share_data.html

The short answer is that there is no good way to do it.

Improve this answer

Comments

0

I got this working. What I did was use a global temporary table. That way I can use it in multiple execute statement rather than if I used a normal temporary table. This way I was also able to join on the global temporary table. Just keep in mind to use a unique id per procedure so that you can tell what rows you are currently working with as this can be seen over several procedures. This way I am also able to delete the rows (based on the id) at the end.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.