I recently saw a comment that described getting data into a temp table using SELECT <Columns> INTO <Table>.  The main reason this was done was just for convenience and to speed up getting to the answers they ultimately needed. For one off analysis or proofing this can be a very useful tool.

However, if you are going to translate that into production ready code there are relatively few places I would want to use a SELECT INTO pattern. I would much rather explicitly declare the table and insert into it. However, I do not want to spend a bunch of time tediously looking up data types and typing out column names, etc.

So what can I do?

Table Structure

You can quickly define a table structure using – you guessed it – SELECT INTO. In order to make it super quick you merely need to add a WHERE 1 = 0 clause as such.

SELECT_INTO

Now to get the definition of your table:

  1. go to your Database
  2. open the Tables folder
  3. Right Click on the Table you selected your query into
  4. Select Script Table as
  5. Select CREATE To
  6. Select an Output, such as Clipboard or New Query Editor Window

SCRIPT_TABLE

This will give you an output similar to

SCRIPT_TABLE_OUTPUT

Now all you have to do is change the table to a temporary table and make any minor modification you need such as adding primary keys.

TEMP_TABLE_MOD

Now you have your table.

 

INSERT INTO

Now you have the table, but you hate to have to type out all of those column names for the insert into. For production code I hope you list the columns in your insert statements so you do not introduce unintended errors when table structures change.

There are a couple easy ways to accomplish this.

If you are using SSDT in visual studio

  1. In your procedure type SELECT * FROM
  2. SELECT_STAR_TEMP_TABLE
  3. Right click on the “*”, Select “Refactor”, Select “Expand Wildcards”
  4. REFACTOR_EXPAND_WILDCARDS
  5. This will pop up a preview window. Press “Apply”
  6. You now have all the columns
  7. COLUMNS_TEMP_TABLE
  8. Modify the SELECT to be an INSERT INTO and paste in your original query
  9. INSERT_INTO_TEMP_TABLE
  10. Remove the INTO statement and the WHERE 1 = 0 and you are all set
  11. SSDT_TEMP_TABLE_INSERT_INTO

If you are using SSMS (SQL Server Management Studio)

  1. Expand the table you created and Drag the “Columns” folder over to your query window
  2. SSMS_COLUMN_COPY
  3. Remove the INTO statement and the WHERE 1 = 0 and you are all set
  4. SSMS_TEMP_TABLE_INSERT_INTO

Hopefully, you find these little tips and tricks can save you some time for what might otherwise be a tedious time consuming process.

Are there other tricks you use to easily turn prototype queries into production deployable code?

Let’s discuss in the comments below.