Labels

Wednesday, 13 November 2019

Generate table definition to match query results

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