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.
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.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT * FROM Sales.Customer INNER JOIN Person.Person ON Customer.PersonID = Person.BusinessEntityID INNER JOIN Person.BusinessEntityAddress ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID INNER JOIN Person.Address ON BusinessEntityAddress.AddressID = Address.AddressID INNER JOIN Person.AddressType ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID; |
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.
|
1 |
CREATE TABLE #temptable ( [CustomerID] int, [PersonID] int, [StoreID] int, [TerritoryID] int, [AccountNumber] varchar(10), [rowguid] uniqueidentifier, [ModifiedDate] datetime, [BusinessEntityID] int, [PersonType] nchar(2), [NameStyle] bit, [Title] nvarchar(8), [FirstName] nvarchar(50), [MiddleName] nvarchar(50), [LastName] nvarchar(50), [Suffix] nvarchar(10), [EmailPromotion] int, [AdditionalContactInfo] xml, [Demographics] xml, [rowguid] uniqueidentifier, [ModifiedDate] datetime, [BusinessEntityID] int, [AddressID] int, [AddressTypeID] int, [rowguid] uniqueidentifier, [ModifiedDate] datetime, [AddressID] int, [AddressLine1] nvarchar(60), [AddressLine2] nvarchar(60), [City] nvarchar(30), [StateProvinceID] int, [PostalCode] nvarchar(15), [SpatialLocation] geography, [rowguid] uniqueidentifier, [ModifiedDate] datetime, [AddressTypeID] int, [Name] nvarchar(50), [rowguid] uniqueidentifier, [ModifiedDate] datetime ) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
/** Summary: > This is a temporary batch for creating such things as table variable, temporary tables or anything else that needs a column list Author: Phil Factor **/ Declare @TheExpression NVARCHAR(MAX)= N'SELECT * FROM Sales.Customer INNER JOIN Person.Person ON Customer.PersonID = Person.BusinessEntityID INNER JOIN Person.BusinessEntityAddress ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID INNER JOIN Person.Address ON BusinessEntityAddress.AddressID = Address.AddressID INNER JOIN Person.AddressType ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;' SELECT 'DECLARE @MyTableVariable table ('+ Stuff ((SELECT ', '+Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name) + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL' --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END AS ThePath FROM sys.dm_exec_describe_first_result_set (@TheExpression, NULL, 0) AS f --(@tsql, @Params, @include_browse_information -- use sys.sp_describe_first_result_set for a batch LEFT OUTER JOIN (SELECT name FROM sys.dm_exec_describe_first_result_set (@TheExpression, NULL, 0) AS f GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames ON DetectDuplicateNames.name=f.name ORDER BY column_ordinal FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')' |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
DECLARE @MyTableVariable table ( CustomerID int NOT NULL, PersonID int NULL, StoreID int NULL, TerritoryID int NULL, AccountNumber varchar(10) NOT NULL, rowguid_6 uniqueidentifier NOT NULL, ModifiedDate_7 datetime NOT NULL, BusinessEntityID_8 int NOT NULL, PersonType nchar(2) NOT NULL, NameStyle bit NOT NULL, Title nvarchar(8) NULL, FirstName nvarchar(50) NOT NULL, MiddleName nvarchar(50) NULL, LastName nvarchar(50) NOT NULL, Suffix nvarchar(10) NULL, EmailPromotion int NOT NULL, AdditionalContactInfo xml NULL, Demographics xml NULL, rowguid_19 uniqueidentifier NOT NULL, ModifiedDate_20 datetime NOT NULL, BusinessEntityID_21 int NOT NULL, AddressID_22 int NOT NULL, AddressTypeID_23 int NOT NULL, rowguid_24 uniqueidentifier NOT NULL, ModifiedDate_25 datetime NOT NULL, AddressID_26 int NOT NULL, AddressLine1 nvarchar(60) NOT NULL, AddressLine2 nvarchar(60) NULL, City nvarchar(30) NOT NULL, StateProvinceID int NOT NULL, PostalCode nvarchar(15) NOT NULL, SpatialLocation geography NULL, rowguid_33 uniqueidentifier NOT NULL, ModifiedDate_34 datetime NOT NULL, AddressTypeID_35 int NOT NULL, Name nvarchar(50) NOT NULL, rowguid_37 uniqueidentifier NOT NULL, ModifiedDate_38 datetime NOT NULL) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
…(Listing 4 here)… INSERT INTO @MyTableVariable SELECT * FROM Sales.Customer INNER JOIN Person.Person ON Customer.PersonID = Person.BusinessEntityID INNER JOIN Person.BusinessEntityAddress ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID INNER JOIN Person.Address ON BusinessEntityAddress.AddressID = Address.AddressID INNER JOIN Person.AddressType ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID; |
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…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
INSERT INTO @MyTableVariable SELECT Customer.CustomerID, Customer.PersonID, Customer.StoreID, Customer.TerritoryID, Customer.AccountNumber, Customer.rowguid, Customer.ModifiedDate, Person.BusinessEntityID, Person.PersonType, Person.NameStyle, Person.Title, Person.FirstName, Person.MiddleName, Person.LastName, Person.Suffix, Person.EmailPromotion, Person.AdditionalContactInfo, Person.Demographics, Person.rowguid, Person.ModifiedDate, BusinessEntityAddress.BusinessEntityID, BusinessEntityAddress.AddressID, BusinessEntityAddress.AddressTypeID, BusinessEntityAddress.rowguid, BusinessEntityAddress.ModifiedDate, Address.AddressID, Address.AddressLine1, Address.AddressLine2, Address.City, Address.StateProvinceID, Address.PostalCode, Address.SpatialLocation, Address.rowguid, Address.ModifiedDate, AddressType.AddressTypeID, AddressType.Name, AddressType.rowguid, AddressType.ModifiedDate FROM Sales.Customer INNER JOIN Person.Person ON Customer.PersonID = Person.BusinessEntityID INNER JOIN Person.BusinessEntityAddress ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID INNER JOIN Person.Address ON BusinessEntityAddress.AddressID = Address.AddressID INNER JOIN Person.AddressType ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
/** Summary: > This is a temporary batch for creating such things as table variable, temporary tables or anything else that needs a column list Author: Phil Factor **/ Declare @TheExpression NVARCHAR(MAX)= N'$SELECTEDTEXT$' SELECT 'DECLARE @$NameOfVariable$ table ('+ Stuff ((SELECT ', '+QuoteName(Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name)) + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL' --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END AS ThePath FROM sys.dm_exec_describe_first_result_set (@TheExpression, NULL, 1)AS f -- use sys.sp_describe_first_result_set for a batch LEFT OUTER JOIN (SELECT name AS name FROM sys.dm_exec_describe_first_result_set (@TheExpression, NULL, 0) WHERE is_hidden=0 GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames ON DetectDuplicateNames.name=f.name WHERE f.is_hidden=0 ORDER BY column_ordinal FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')' |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
/** Summary: > This is a temporary batch for creating such things as table variable, temporary tables or anything else that needs a column list Author: Phil Factor **/ Declare @TheExpression NVARCHAR(MAX)= N'uspGetManagerEmployees 1' SELECT 'DECLARE @MyTableVariable table ('+ Stuff ((SELECT ', '+Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name) + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL' --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END AS ThePath FROM sys.dm_exec_describe_first_result_set (@TheExpression, NULL, 0) AS f --(@tsql, @Params, @include_browse_information -- use sys.sp_describe_first_result_set for a batch LEFT OUTER JOIN (SELECT name FROM sys.dm_exec_describe_first_result_set (@TheExpression, NULL, 0) AS f GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames ON DetectDuplicateNames.name=f.name ORDER BY column_ordinal FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')' |
Listing 8
Execute it, and you will get the result
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @MyTableVariable TABLE ( RecursionLevel INT NULL, OrganizationNode NVARCHAR(4000) NULL, ManagerFirstName NVARCHAR(50) NOT NULL, ManagerLastName NVARCHAR(50) NOT NULL, BusinessEntityID INT NULL, FirstName NVARCHAR(50) NULL, LastName NVARCHAR(50) NULL ) |
So, you just add this, in the same batch …
|
1 2 3 |
INSERT INTO @MyTableVariable EXECUTE uspGetManagerEmployees 1 SELECT * FROM @MyTableVariable --just to check! |
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 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
You may also like
-
Live training session
The Hidden Treasures of SQL Prompt
In this session Grant Fritchey, Redgate Advocate and Tom Walsh from the Redgate SQL Prompt development team will walk you through the treasure map of SQL Prompt and show you where all the great features are buried.
-
Article
Avoid T-SQL Technical Debt using SQL Prompt Code Analysis
SQL Prompt can help prevent the build-up of technical debt in your database code. It alerts the team immediately to issues that might not prevent your code from working as expected, but could eventually cause problems with performance, or maintenance, or even security.
-
Live training session
More Gems from the SQL Prompt Treasure Chest
Join Grant Fritchey, a Redgate Advocate and Owen Standage from the Redgate SQL Prompt development team to see some more of the features you may not be using and become the captain of your SQL Prompt ship. Some of the gems we'll be demonstrating are: Azure Connections, Smart Rename, Split Table, Find invalid objects, Summarise Script, Script as alter, Find unused variables, Column picker.
-
Article
Enhancing your database development using coding standards
Updated June 2020 Development and IT departments face increasing demands to deliver changes faster, often with increasing complexity and less tolerance for downtime, and without a corresponding growth in team size to handle the extra work. Standardizing database development practices by establishing coding standards can help achieve this by removing blockers to understanding code, easing
-
