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:
  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
  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:

    How to use the Query Designer with a temporary Table

    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.

    How to use the Query Designer with a temporary Table

    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

Subscribe To Our Newsletter and get our Ultimate Web Design Resource list for free!

You have Successfully Subscribed!

Subscribe To Our Newsletter and get our Ultimate Web Design Resource List for free! Whether your starting a blog or your a pro web designer, its FULL of useful resources !

Subscribe To Our Newsletter and get our Ultimate Web Design Resource List for free! Whether your starting a blog or your a pro web designer, its FULL of useful resources !

You have successfully subscribed! Confirm your email address for the free resource list!

Pin It on Pinterest

Share This