Creating an empty table from a SELECT statement

10

November 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.

About these ads

10 thoughts on “Creating an empty table from a SELECT statement

  1. David McKinney. says:

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

      • David McKinney. says:

        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.

  2. Robert Willsie says:

    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…”

  3. compiux says:

    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!

      • compiux says:

        Thank you very much for the answer :)
        Things are clear now :D . I will check the videos you mentioned n___n

    • Diego Buendia - BCN Spain says:

      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.

  4. […] 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 […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 491 other followers

%d bloggers like this: