Creating an empty table from a SELECT statement
12November 26, 2012 by Kenneth Fisher
I’m in the middle of reading a good book on DMVs, “SQL Server DMVs in Action” http://www.manning.com/stirk by Ian W. Stirk. In it he points out that you can create a temporary table from a query and use it to store the data from multiple runs of the query. This is a very handy technique and one I’ve been using for years. Ian recommends that you had a simple 1=2 to the WHERE clause of the query, and of course an INTO clause. So the query:
SELECT members.name AS membername, members.principal_id, members.type_desc, members.default_schema_name, roles.name AS rolename FROM sys.database_principals members LEFT OUTER JOIN sys.database_role_members xref ON members.principal_id = xref.member_principal_id LEFT OUTER JOIN sys.database_principals roles ON xref.role_principal_id = roles.principal_id WHERE members.type_desc = 'SQL_USER'
can be changed to
SELECT members.name AS membername, members.principal_id, members.type_desc, members.default_schema_name, roles.name AS rolename INTO #UsersnRoles FROM sys.database_principals members LEFT OUTER JOIN sys.database_role_members xref ON members.principal_id = xref.member_principal_id LEFT OUTER JOIN sys.database_principals roles ON xref.role_principal_id = roles.principal_id WHERE members.type_desc = 'SQL_USER' AND 1=2
This will create a temporary table #UsersnRoles that has the correct structure to hold the data from my query. This method is quick, because, as Ian mentions in his book, SQL only really looks at the structure of the tables. Code using it is also low maintenance because if the underlying tables change then so will the temp table (on the next run of course).
Given that there are lots of ways to do virtually anything in SQL, it won’t surprise anyone that this isn’t the method I use. Let’s say instead the query was:
SELECT members.name AS membername, members.principal_id, members.type_desc, members.default_schema_name, roles.name AS rolename FROM sys.database_principals members LEFT OUTER JOIN sys.database_role_members xref ON members.principal_id = xref.member_principal_id LEFT OUTER JOIN sys.database_principals roles ON xref.role_principal_id = roles.principal_id WHERE members.type_desc = 'SQL_USER' OR roles.name = ' db_owner'
Now I have a slight, and admittedly very slight, problem. Because I have an OR in my WHERE clause I now have to go back and put in parens before I add the “AND 1=2” or I won’t get the correct results. Also please remember that this is a very simple example. I’ve had cases where my WHERE clause was a dozen lines long or longer and adding “AND 1=2” was more complicated than it was worth. Because of this I generally prefer to use TOP 0 instead. Using this method the query looks like this:
SELECT TOP 0 members.name AS membername, members.principal_id, members.type_desc, members.default_schema_name, roles.name AS rolename INTO #UsersnRoles FROM sys.database_principals members LEFT OUTER JOIN sys.database_role_members xref ON members.principal_id = xref.member_principal_id LEFT OUTER JOIN sys.database_principals roles ON xref.role_principal_id = roles.principal_id WHERE members.type_desc = 'SQL_USER' OR roles.name = ' db_owner'
I’ve found this to be just as easy but without the possible “gotcha” the 1=2 method has.
I should probably mention for those who haven’t run this type of query before that if querying a single table this method will create tables that include identity columns, but not column level constraints (haven’t tried table), defaults, foreign & primary keys, or indexes from the parent table. If multiple tables are used the identity column is excluded also. I’ve found this handy when creating archive tables.
SELECT TOP 0 * INTO tableNameArchive FROM tableName
I still have to add in any defaults, constraints etc that I want, and sometimes I have to remove or rename the identity column. But it’s a quick and easy starting point.
SQL Studies 
Have I missed something here?
The WHERE 1=2 method is to generate an empty table with the same structure as the select (same fields / definitions etc.) hence the rest of the where clause is irrelevant.
What I mean is that you can simply replace the existing where clause with your WHERE 1=2, because in no case will the where clause change the structure of the result – just the number of rows returned.
I’m probably missing the point?
Certainly you can just replace the WHERE clause. You can also just wrap the whole thing up in a CTE then do a SELECT * FROM cte WHERE 1=2. Honestly I can think of several other ways to accomplish the same thing as well. In this particular case the author was adding the 1=2. Personally I still prefer the TOP 0 method because it requires fewer changes to the original query. However I’ve used the CTE method to pass in a query and do it dynamically and I’ve used the 1=2 method, both replacing the WHERE clause and not, over the years as well.
I’m not sure you were missing anything. There are just so many ways to get to the same place no one person is going to think of them all. And to be very honest I didn’t think to mention that option. So Thanks! 🙂
True that ‘all these roads lead to Rome’; the only thing one should check to be thoroughly thorough(!) is that the execution plans are similarly simple. i.e. that no scenario is retrieving all the data prior to trashing it. Probably the sql optimiser is sufficiently enlightened to take care of this, but still best to check.
Good point. Would suck to put something into production and discover under full load you just added 30 minutes to your process.
Due to poor math skills, I learned years ago to enclose any formulas in parens to make sure they calculated correctly. I carried that over into programming many years ago and have found it taks very little additional time and frequently results in much less trouble shooting of where statements.
I like your Top 0 idea though. I still do minor mental gyrations when I run into code that says “Where 1=1…” or “Where 1=2…”
Hi, I’ve been working with SQL Server for some time now, but every day I amaze myself at how little I know about it haha.
I liked your post quite a lot. I used to do something like that in my C# code when I wanted some custom table but never knew how efficent that was and how it affected my performance (Yes, shame on me). When I saw your post I was like “Hey! that’s a great idea” but David pointed something that left me wondering.
Why do you add all the WHERE clausules?
I mean, you just want the column structures isn’t it? So, what’s the point of adding all the JOINS? Isn’t it going to add proccessing time even if it does not bring any data?
What difference would there be if I type:
01 SELECT members.name AS membername, members.principal_id, members.type_desc,
02 members.default_schema_name, roles.name AS rolename
03 INTO #UsersnRoles
04 FROM sys.database_principals members
05 WHERE 1=2
isntead of the full query?
Just wondering. I think it is time that I stop doing things “By feeling” and start to understand what really is happening behind my queries.
Honestly there is no really good reason to add the WHERE clause. It makes a fractional (a really really small fraction) difference in the easy of copy and paste. Basically you don’t have to worry about where the end of your query is. Also if I’m doing the work manually I’ll frequently put TOP 0 at the top of the query, add the INTO, run the thing, then back those 2 bits out again. If you prefer to trim off the WHERE clause, then add WHERE 1=2 that is entirely up to you. One of the things I enjoy the most with SQL is that everyone does things a little bit differently, so if I watch closely enough I’ll learn something new. For example I have used 1=2, TOP 0, etc etc for the majority of the time I’ve used SQL (over 12 years) but I’d never thought to look at the execution plan to see WHY it was fast.
FYI I started trying to understand things just a couple of years ago myself. It’s been a fun journey so far with no end in sight. You might try viewing some of the MCM videos if you are interested. Some of them can be a bit hard to follow unless you are really familiar with the subject but they all contain a lot of very interesting information. Best of all they are free!
Thank you very much for the answer 🙂
Things are clear now 😀 . I will check the videos you mentioned n___n
Reason may be you want to get columns from several tables. By the way, I prefer TOP 0 syntax. I anguish reading WHERE 1=2.
[…] doesn’t look much shorter, but that is because the first statement is creating an empty “storage” table. In terms of time and performance it’s basically a null statement. It checks the schema of the […]
[…] what’s the problem? Well, to start with by using the SELECT TOP 0 INTO Archive method of creating the Archive table you’ve created the archive table with an identity […]
[…] while back I did a post about creating an empty table using a SELECT statement. Basically doing something like […]