#Redgate sql prompt formatter batch code#

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). Doing it by hand: a temporary batch to create the column list It may be the result thrown at you from a stored procedure that you canвЂ™t alter, or a wild view. Bear with me: IвЂ™m just imagining the horror. No, the destination table needs unique column names and youвЂ™ve also still got the problem of the XML typed schema collections. Useful in most circumstances, but not quite what you need here because it would no longer be SELECT *.

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. In this case, both the rowguid and ModifiedDate column appears five times. 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. It allows for duplicate column names in the results, and you canвЂ™t even do a SELECTвЂ¦INTO with that.

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. Column name 'rowguid' in table '#temptable' is specified more than once. You highlight just the CREATE TABLE part of the script, and run it.Д¬REATE TABLE # temptable ( int, int, int, int, varchar ( 10 ), uniqueidentifier, datetime, int, nchar ( 2 ), bit, nvarchar ( 8 ), nvarchar ( 50 ), nvarchar ( 50 ), nvarchar ( 50 ), nvarchar ( 10 ), int, xml, xml, uniqueidentifier, datetime, int, int, int, uniqueidentifier, datetime, int, nvarchar ( 60 ), nvarchar ( 60 ), nvarchar ( 30 ), int, nvarchar ( 15 ), geography, uniqueidentifier, datetime, int, nvarchar ( 50 ), uniqueidentifier, datetime )Д«ang! Msg 2705, Level 16, State 3, Line 1Д¬olumn names in each table must be unique. A new query pane opens with the code to insert the results into a table.Д®xcellent. 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вЂ™. We just want to create the temporary table from the INSERT script that is generated.Дeftly, you check that the result pane is set to вЂgridвЂ™ ( Query | Results to), replace the SELECT * with SELECT TOP 1 *, and hit execute. We can get a table build script using SQL PromptвЂ™s versatile вЂњScript as insertвЂќ feature. Creating a table build script using PromptвЂ™s вЂњScript as InsertвЂќ feature It isnвЂ™t quick, and errors creep in easily. It cannot directly create a table variable or table-valued parameter.ДЄlso, 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. More generally, however, SELECT INTO only works with the result of a query, not a batch or procedure. 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. 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.ДЄnd there was you thinking, вЂњ I just wanted to get home early today!вЂ Why not just use SELECTвЂ¦INTOвЂ¦? You try the SQL Prompt column picker ( Ctrl+space).

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