Building reusable table build scripts using SQL Prompt

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!

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

Ah, but wait…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

Bang!

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

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!

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!

Conclusions

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 favourite 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!

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.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Hub

Documenting your Database with SQL Change Automation

It would be wrong to portray SQL Change Automation (SCA) as being suitable only for epic project deployments, of the sort described in my previous article. It can do smaller tasks as well. To demonstr...

Also in SQL Prompt

SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020)

The SQL query that is used to produce the result that is inserted into the permanent table has its order specified by an ORDER BY statement. Relational tables are not ordered, so the ORDER BY is meani...

Also in Product learning

Spoofing Realistic Credit Card Data for your Test Systems using Data Masker

Data protection and privacy regulations, ranging from GDPR to HIPPAA to PCI, among many others, put strict compliance requirements on the storage and use of personal and sensitive data, in any of your...

Also about SQL Prompt snippets

Customizing the SQL Prompt built-in snippets: a better 'ata' snippet

Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for p...

  • Tom

    Way too complicated. Just add an INTO after the SELECT * and it will create the table. You probably also want to add a “WHERE 1=0” so it will not populate the table structure with any data. You can tweak the column nullability, indexes, etc if you want to, via SSMS.

    • Phil Factor

      Thanks for the suggestion.
      Perhaps I didn’t make clear enough the purpose of this. I’ll agree that you can usually create a table from a query via Select into, though I’ve demonstrated in this article that even this can fail: But what if you are writing a routine that creates a work table of some sort? For a start, SELECT INTO only works with the result of a query, not a batch or procedure. It can’t directly create a table variable or TVP. Also, If you need to write a process that creates any sort of table from a query, you would need surely need to SELECT INTO an ordinary table, then get the source of the resulting table via SSMS, and massage the text of the DDL Code you get back in order to write the CREATE statement. I used to do that before these handy DMFs came along. For batches and procedures, the quickest trick was to copy the headers from the results grid, and try to remember or check the data type. It wasn’t quick and errors crept in.

      • Tom

        Yep, you are right – I did not read that far.

  • Mike Tessier

    I am working through this example, but don’t understand the “Script as Insert” I click on the row and then right-click on that row and all I see is option for copy. I’m using SQL Server Management Studio. Any ideas?

    • Phil Factor

      Sorry if I’ve explained it badly. If you have a reasonably recent version of SQL Prompt installed and enabled in SSMS, (check the Prompt ->Options window) and you have selected the grid view for the results pane, you should see a context menu with the menu items ‘Copy. Copy with Headers, Select All, Script as INSERT, Open in Excel, Save Results as …, Page Setup …, and Print ….’. SQL Prompt adds ‘Script as INSERT and Open in Excel,’ to the existing context menu. If the ‘Script as INSERT’ option doesn’t show up, please contact the RedGate developers on the forum pages here. https://forum.red-gate.com/categories/sql-prompt