Create a table using a dynamic complex query

Question

Is there a way to create a table from a complex query result set? A parameter will contain a query and I need to take this query and put the result into a temporary table.

I have it working for a simple query

Declare @Src nvarchar(4000)='Select Distinct a,b,c from mytable'
Declare @newId VARCHAR(50)

SELECT @newId = REPLACE(CONVERT(VARCHAR(50),NEWID()),'-','')

-- Make sure the table doesn't exist... if does we need to delete it. 
IF OBJECT_ID('TMP_' + @newId) IS NOT NULL 
BEGIN 
    SET @SQLStr = 'DROP TABLE TMP_' + @newId
    EXEC (@SQLStr)
END 
-- I might need the structucture or the result set or the results in the tmp table.
SET @SQLStr = 'SELECT * INTO TMP_' + @newId + ' FROM (' + @SRC + ') S WHERE 1=0'
EXEC(@SQLStr)

but what if @src is with an 'order by' or with subqueries

Select a,b,c from mytable order by a,b

I would need to find the "from" and add "into 'TMP_' + @newId ' in front of it but it isn't safe as you can have subqueries in the fields portion of a select.

I tried to use the sp_describe_first_result_set @Tsql=@Src, but as far as I know, I would need to parse the results in a cursor to build a create table statement, execute it, so that I can then insert the complex query using:

EXEC('Inset into tmp_'+ @newId + ' 
exec('+@src+')');

Any other way to simplify this? I'm trying to find a solution that will work for all version SQL servers (express or Standard).

Unless you want to make significantly simplifying assumptions, the short answer is no. If you allow any select query as a starting point, then you have a very, VERY complex goal of parsing the query and finding the correct position within the string to inject your into clause. A query involving a simple CTE will be a challenge. And TSQL is not well suited for this. — SMor, Jan 30 '20 at 22:22
And note that "version" and "edition" are not the same; both can affect the functionality that might limit any solution. — SMor, Jan 30 '20 at 22:24

score 1 · Accepted Answer · answered Jan 31 '20 at 06:13

I would try it with "sys.sp_describe_first_result_set" like this. You do not need a cursor.

Example:

Drop  table if exists #ResultStructure
Drop  table if exists #test

create table #test (
    column1 int,
    column2 int
);

create table #ResultStructure (is_hidden bit NOT NULL
, column_ordinal int NOT NULL
, name sysname NULL
, is_nullable bit NOT NULL
, system_type_id int NOT NULL
, system_type_name nvarchar(256) NULL
, max_length smallint NOT NULL
, precision tinyint NOT NULL
, scale tinyint NOT NULL
, collation_name sysname NULL
, user_type_id int NULL
, user_type_database sysname NULL
, user_type_schema sysname NULL
, user_type_name sysname NULL
, assembly_qualified_type_name nvarchar(4000)
, xml_collection_id int NULL
, xml_collection_database sysname NULL
, xml_collection_schema sysname NULL
, xml_collection_name sysname NULL
, is_xml_document bit NOT NULL
, is_case_sensitive bit NOT NULL
, is_fixed_length_clr_type bit NOT NULL
, source_server sysname NULL
, source_database sysname NULL
, source_schema sysname NULL
, source_table sysname NULL
, source_column sysname NULL
, is_identity_column bit NULL
, is_part_of_unique_key bit NULL
, is_updateable bit NULL
, is_computed_column bit NULL
, is_sparse_column_set bit NULL
, ordinal_in_order_by_list smallint NULL
, order_by_list_length smallint NULL
, order_by_is_descending smallint NULL
, tds_type_id int NOT NULL
, tds_length int NOT NULL
, tds_collation_id int NULL
, tds_collation_sort_id tinyint NULL
);
DECLARE @SQLStr nvarchar(max)
Declare @Src nvarchar(4000)='select * from #test order by column1'
Declare @newId VARCHAR(50)

SELECT @newId = REPLACE(CONVERT(VARCHAR(50),NEWID()),'-','')
Insert #ResultStructure
exec sys.sp_describe_first_result_set @Src;

select @SQLStr = STRING_AGG(#row,'') FROM ( 
    select 
      case when column_ordinal = 1 then 'create table TMP_' + @newId + '(' else ', ' end
        + QUOTENAME (name) + ' ' + system_type_name
        + case when column_ordinal = max(column_ordinal) over () then ');' else '' 
      end as #row
     from #ResultStructure
 ) T
print @SQLStr
--EXEC(@SQLStr)

This is great! the only problem is the STRING_AGG... It was only introduced in SQL Server 2017... I have to find the equivalent in previous versions for it to be compatible with SQL 2008 and up — JohnG, Jan 31 '20 at 14:17
I've made your script compatible with older versions but now, I need to make sure that the records are inserted in the order by clause of @src (if present)... to do so, I tried to do it in the same way you did the STRING_AGG adding an order by on ordinal_in_order_by_list (using the order_by_list_length to get ASC ro DESC) of the #ResultStructure... but I can't get it to work... Any idea? — JohnG, Feb 03 '20 at 19:06
I do not think this is possible, a table has no order. Here a little more on the topic: [link](https://stackoverflow.com/questions/14424929/preserving-order-by-in-select-into). Or did I misunderstand something? — Robin, Feb 04 '20 at 05:50
Thank you! To preserve the order of the insert into the created table, I used an identity column so that if there's an order by in the complex Select statement, it will preserve the order that the data was inserted in. — JohnG, Feb 04 '20 at 14:10

score 0 · Answer 2 · answered Jan 30 '20 at 23:07

0

One way to do this would be to replace all 'SELECT ' strings with 'SELECT TOP 100 PERCENT '. The ORDER BY clause is valid in subqueries is valid if the SELECT has a TOP * clause.

SET @SQLStr = 'SELECT * INTO TMP_' + @newId + ' FROM (' + REPLACE(@SRC, 'SELECT ', 'SELECT TOP 100 PERCENT ') + ') S WHERE 1=0'
EXEC(@SQLStr)

answered Jan 30 '20 at 23:07

Laughing Vergil

3,496
1
12
25

But the source query could already contain a top clause. Then this would not work. – Robin Jan 31 '20 at 05:52

Create a table using a dynamic complex query

2 Answers2