CODEWITHSUNDEEP
sql-servert-sql
JohnG
JohnG

Reputation: 272

Create a table using a dynamic complex query

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

Upvotes: 0

Views: 447

Answers (2)

Robin
Robin

Reputation: 154

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)

Upvotes: 1

Laughing Vergil
Laughing Vergil

Reputation: 3756

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)

Upvotes: 0

Related Questions