Product articles SQL Prompt SQL Code Snippets
Building reusable table build scripts…

Building reusable table build scripts using SQL Prompt

You need a fast, general-purpose way to save the results of a query or batch or procedure into any sort of worktable, such as a temporary table or a table variable or table valued parameter. A simple SELECT…INTO isn't versatile enough for these requirements, and the alternative ways to handcraft the list of columns are slow and error prone. Phil Factor shows how to create a 'table-build generator' that will do all this, and save you a lot of time, especially if you use a lot of working tables in your code.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

You’ve been working on a query, function or procedure and you want to store the results of executing it in a table; maybe a table variable, or a temporary table. These all require column definitions, and such things can be tiresome to do. Consider the following AdventureWorks query.

Listing 1

You need to store the results of this query, probably using an INSERT statement but first you need to create a temporary table or table variable. How do you do it? It sounds simple enough; you just need a CREATE TABLE or DECLARE…TABLE statement to create a temporary table or table variable and pop the results into it. You try the SQL Prompt column picker (Ctrl+space). Nope, that just gives you just the column names; to create a temporary table or table variable you also need a list of the types and the nullability (NULL or NOT NULL) for each column, and they’ve got to be right, and in the correct order.

And there was you thinking, “I just wanted to get home early today!

Why not just use SELECT…INTO…?

If you simply tried SELECT * INTO Sales.CustomerDetails FROM Sales.Customer… you’d hit a specific problem with duplicate column names, in this case, which I’ll explain shortly. More generally, however, SELECT INTO only works with the result of a query, not a batch or procedure. It cannot directly create a table variable or table-valued parameter.

Also, if you need to write a process that creates any sort of table from a query, you’d need to SELECT INTO an ordinary table, then get the source of the resulting table via SSMS and then massage the text of the DDL code you get back in order to write the CREATE statement. It isn’t quick, and errors creep in easily.

Creating a table build script using Prompt’s “Script as Insert” feature

We can get a table build script using SQL Prompt’s versatile “Script as insert” feature. We just want to create the temporary table from the INSERT script that is generated.

Deftly, you check that the result pane is set to ‘grid’ (Query | Results to), replace the SELECT * with SELECT TOP 1 *, and hit execute. Select the entire row in the grid by clicking on the the empty top left square of the grid (row 1 – column 1 cell if you are a spreadsheet freak), then right-click on the selected row(s) to get the context menu and hit ‘Script as INSERT’. A new query pane opens with the code to insert the results into a table.

Excellent. You highlight just the CREATE TABLE part of the script, and run it.

Listing 2


Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'rowguid' in table '#temptable' is specified more than once.

What’s gone wrong? The use of SELECT *, which should normally give you a twinge of conscience, was there deliberately in this case, because it makes it more awkward for us. It allows for duplicate column names in the results, and you can’t even do a SELECT…INTO with that. There are typed XML columns too, which will also trigger an ‘XML column xxx is typed with a schema collection' error, if you are using a temporary table, even if there were no duplicate column names.

In this case, both the rowguid and ModifiedDate column appears five times. Prompt’s action menu gives you the option to replace that SELECT * with a list of the columns so that you could then delete the duplicate columns, or you can select just the columns you want from the column-picker. Useful in most circumstances, but not quite what you need here because it would no longer be SELECT *. No, the destination table needs unique column names and you’ve also still got the problem of the XML typed schema collections. Bear with me: I’m just imagining the horror. It may be the result thrown at you from a stored procedure that you can’t alter, or a wild view. Our query is just an example.

Doing it by hand: a temporary batch to create the column list

OK, maybe you can still get home in time, if you are on SQL Server 2012 or later, but you’ll need to do it the harder way, and create some code to fix duplicate names, by giving each of the five rowguid columns a unique number (the column position). Quick as a flash you produce this.

Listing 3

This script uses the sys.dm_exec_describe_first_result_set dynamic management function (or the similar sys.sp_describe_first_result_set for a batch), which allows you to get the metadata from a range of objects. You provide the expression containing the T-SQL statements, or batch, and a description of any embedded parameters, and it returns the names of the columns, their ordinal position, nullability, data types and more. We want more than just base table; we want to know what is returned by a query on any combination of table-sources or the first result from a batch. We also want to know what is returned by procedures or triggers when you execute them.

I use sys.dm_exec_describe_first_result_set twice, first to test for duplicate names, and then to get a column definition for each column. I then glue each column definition together into a table build script, using the XML concatenation trick.

Let’s give this a try. In SSMS, if you want the build script formatted with line breaks, make sure that you’ve set Query | Results to | Results to Text, and you’ve allowed a nice long value for Query | Query Options | Results | Text | Maximum number of characters displayed in each column (8192 will suffice). We execute the code and get this in the result pane of SSMS.

Listing 4

It is a lot, but then we are using an extreme example just to prove the concept. Append Listing 5 directly to the end of the table variable creation script in Listing 4, and try it out.

Listing 5

And if all is well it comes back with is (18508 rows affected).

As you have SQL Prompt, you would look around furtively, highlight that * and hit the TAB key to expand the wildcard into individual column names (or use the Expand Wildcards action from the Action list), just so it all looks nice. You’ll end up with…

Listing 6

Even though you haven’t aliased the duplicate names, you won’t have duplicate names in the destination table, so everything will work. In this particular case, you could, of course, have used the column picker to get just the columns you really need, but in the sort of scenario we’re imagining, you can’t fix a routine that is throwing you this result.

OK. You may get home early after all.

Creating a helper routine using a SQL Prompt snippet

You may be looking back at that phrase earlier in the article, and asking yourself ‘What did he mean by ‘quick as a flash‘? Well, because my memory is splendid, but only for remembering such things as where the jar with the chocolate biscuits is, I’d already saved the code for removing column duplicates as a ‘helper’ routine in my SQL Prompt snippet library.

Here is the code to create the snippet.

Listing 7

In SSMS highlight the code in listing 7, right-click and choose Create Snippet. Give the snippet a name (I used tvc, standing for Table Variable Creator) and description, and hit Save. I’ve already explained all about how to use Prompt Snippets in another article, so see Templates and Snippets in SSMS and SQL Prompt for a full description.

SQL Prompt Snippet for saving query results to table variables

You should add a good default value for the placeholder, @NameOfVariable, which conforms to your house style.

Now all you need to do is highlight Listing 1, find the snippet in the Prompt Action list (start typing its name) and click on it. The result will be the table variable declaration in Listing 3.

If you want to create a base table or a temporary table suitable for receiving the output of any batch, procedure or query, all you need to do is to edit the DECLARE @$NameOfVariable$ table to CREATE TABLE #NameOfTable for a temporary table or CREATE TABLE schema.NameOfTable for an ordinary table.

Let’s just try it once more, this time selecting a procedure. If you want the DECLARE script nicely formatted, make sure that the SSMS results pane is set for text rather than grid and that you are set to receive plenty of text (see above). Otherwise it is fine to select the result from the grid.

In SSMS, tap in the code to execute the uspGetManagerEmployees stored procedure (but without the EXEC statement in this example because it is the start of a batch, e.g. uspGetManagerEmployees 1, which works well with AdventureWorks2012).

Highlight this code, invoke your snippet. You should see this:

Listing 8

Execute it, and you will get the result

So, you just add this, in the same batch …

Listing 9

And success!

esting the table variable creator

So, clean up by deleting the batch you created to give you the table build script (highlighted in the screenshot below, just as I am about to hit the delete key) and you can now slip home early!

Saving Query Results To a WorkTable


SQL Prompt will do most of the mindless tasks that you’d otherwise be obliged to undertake by hand, such as reformatting code or generating lists of column-names. This will hopefully leave more time for the more demanding and interesting jobs.

SQL Prompt snippets provide a great way of using your favorite time-saving routines as snippets. In this case, a good table-build generator will make things easier and more accurate for your work, especially if you use a lot of working tables in your code. You can, of course, very quickly turn it into a table-valued function to put in your utilities directory but to have it as a snippet means that you can just delete the generated code when you’ve used it. The choice is yours!

Original publication date: May 25 2018

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more