How to use the Query Designer with Temporary Tables

I often have to write big and complex queries in my day job and so using the Query Designer in SQL Server Management Studio makes this a lot easier to work with. One problem that i have found with it is that it doesn’t seem to like temporary tables but i have found out how to Use the query designer with temporary tables which i will explain here. I will use the Northwinds example database.

If you don’t want to watch the video then please jump below for the full code and transcript:

How to use the Query Designer with Temporary Tables

1. Declare a temporary table like below:

   DECLARE @temp table(ID int, Name varchar(50))

2. Now we will keep this simple for now but this will work on more complex queries in the exact same way. For example we will select from the orders table and want to join our temp table to this. So define your basic query without the temporary table

   SELECT OrderID, CustomerID, ShippedDate
   FROM Orders

3. Now when in the designer of the query if you try to do an inner join to your table by hand you can achieve the following which is not very practical when you have lots of tables or lots of joins because you can not see the columns defined in your temporary table:

   SELECT Orders.OrderID, Orders.CustomerID, Orders.ShippedDate
   FROM Orders INNER JOIN
    [@temp] ON Orders.OrderID = [@temp].ID

   

   How to use the Query Designer with a temporary Table

4. So what we are going to do is fool the Query Designer and the way we will do this is by using a Derived table. It appears that the query designer does not look into the derived table to check its existence.

   SELECT Orders.OrderID, Orders.CustomerID, Orders.ShippedDate
   FROM Orders INNER JOIN
    (SELECT ID, Name
    FROM [@temp]) AS derivedtbl_1 ON Orders.OrderID = derivedtbl_1.ID

   

   How to use the Query Designer with a temporary Table

   You can now drag and drop joins as if this was a non temporary table. Please note it is sensitive about the square brackets being around the table name. If these are removed the designer will kick up a fuss.

    

Comments

comments