I used to do below code during code tuning like instead of loading data into temp table from select statement directly I used to create temp table structure from the table or query results by passing into @query variable and then use insert into temp table for select query results.
SET NOCOUNT ON; DECLARE @Query VARCHAR(MAX) = 'select * from dbname.schemaname.tablename;'; DECLARE @TempTableName VARCHAR(128) = '#temptable' DECLARE @ColumnList VARCHAR(MAX); SELECT @ColumnList = STUFF(( SELECT ' , ' + name + ' ' + system_type_name + ' '
+ CASE is_nullable WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END + CHAR(10) FROM sys.dm_exec_describe_first_result_set(@Query, NULL, 0) FOR XML PATH('') ), 1, 1, ''); PRINT 'CREATE TABLE ' + @TempTableName + '(' PRINT @ColumnList; PRINT (');');
No comments:
Post a Comment