SELECT query to table var/temp table/CTE (SQL Server 2k8)

Creates a table variable or temporary table from the selected query (if it makes sense, of course).

Code:

$PROMPT(method,Table variable or Temporary table?,,,variable temporary,"Table variable" "Temporary table")$
$PROMPT(name,Table variable name?,sa_table)$
$$
SET NOCOUNT ON

DECLARE @prefix CHAR(1)
SELECT @prefix =
CASE '$method$'
   WHEN 'variable' THEN '@'
   WHEN 'temporary' then '#'
   ELSE '?'
END

IF OBJECT_ID( 'tempdb.dbo.$name$' ) IS NOT NULL
DROP TABLE tempdb.dbo.$name$

SELECT *
INTO tempdb.dbo.$name$
FROM
($CURRENT_SEL$) AS ganyolas
WHERE 1 = 0

DECLARE @sql NVARCHAR(MAX)
SELECT @sql =
CASE '$method$'
WHEN 'variable' THEN 'DECLARE @$name$ TABLE '
WHEN 'temporary' THEN 'CREATE TABLE #$name$ '
END + CHAR(10) + '(' + CHAR(10)

SELECT
@sql = @sql
+ CASE
   WHEN c.column_id = 1 THEN ' '
   ELSE ' ,'
   END
+ c.[name] + ' '
+ CASE
   WHEN st.[name] LIKE '%char%'
      OR st.[name] LIKE '%binary%'
      OR st.[name] LIKE '%decimal%'
         THEN UPPER( st.[name] ) + '(' + CAST( c.max_length AS VARCHAR( 64 ) ) + ')'
   ELSE UPPER( st.[name] )
   END
+ CASE c.is_nullable
   WHEN 0 THEN ' NOT NULL'
   WHEN 1 THEN ' NULL'
   ELSE 'NULLGEBASZ'
   END
+ CASE
   WHEN RANK() OVER( PARTITION BY t.[name] ORDER BY c.column_id DESC ) = 1 THEN ''
   ELSE CHAR( 10 )
   END
FROM
tempdb.sys.tables t
INNER JOIN
tempdb.sys.[columns] c
   ON c.[object_id] = t.[object_id]
INNER JOIN
sys.systypes st
   ON st.xtype = c.system_type_id
WHERE t.[name] = '$name$' AND st.[name] != 'sysname'
ORDER BY c.column_id ASC

DROP TABLE tempdb.dbo.$name$

SELECT @sql = @sql + CHAR(10) + ')' + CHAR(10)

SELECT @sql = @sql
+ 'INSERT INTO ' + @prefix + '$name$'SELECT @sql$$
$CURRENT_SEL$

$$
SELECT 'SELECT * FROM ' + CASE '$method$' WHEN 'variable' THEN '@' WHEN 'temporary' THEN '#' ELSE '?' END + '$name$' + CHAR(10)

SELECT CASE '$method$' WHEN 'variable' THEN '' WHEN 'temporary' THEN CHAR(10) + 'DROP TABLE #$name$' ELSE 'dropgerror' END
$$

The same with CTE.

Code:

$PROMPT(ctename,CTE name?,cte)$
$$
SET NOCOUNT ON

IF OBJECT_ID( 'tempdb.dbo.$ctename$' ) IS NOT NULL
DROP TABLE tempdb.dbo.$ctename$

SELECT *
INTO tempdb.dbo.$ctename$
FROM
($CURRENT_SEL$) AS ganyolas
WHERE 1 = 0

DECLARE @sql NVARCHAR( MAX ) = ';WITH $ctename$ ( '

SELECT
@sql = @sql
+ c.[name]
+ CASE
   WHEN RANK() OVER( PARTITION BY t.name ORDER BY c.column_id DESC ) = 1 THEN ' )'
   ELSE ','
   END
FROM
tempdb.sys.tables t
INNER JOIN
tempdb.sys.[columns] c
   ON c.[object_id] = t.[object_id]
INNER JOIN
sys.systypes st
   ON st.xtype = c.system_type_id
WHERE t.[name] = '$ctename$' AND st.[name] != 'sysname'
ORDER BY c.column_id ASC

DROP TABLE tempdb.dbo.$ctename$

SELECT @sql$$
AS
(
$CURRENT_SEL$
)
SELECT * FROM $ctename$

Or all the three, put into one complex alt+num triggered snippet, using the new '$CURRENT(selection, escape_quotes)$' feature. You should also put the query that's to be transformed between comments --4b and --4e.

Code:

$PROMPT(@method,Use query as cte\,variable\,temporary table or send mail? Type?,,Use query as cte\,variable\,temporary table or send mail,CTE "Table variable" "Temporary table" "Send mail")$
$PROMPT(@table_name,Table name (use # for global temp table)?,sa_table,Use query as cte or variable)$
$$
SET NOCOUNT ON
SET ARITHABORT ON

DECLARE @header NVARCHAR(MAX) = ''
,@footer NVARCHAR(MAX) = ''
,@columns NVARCHAR(MAX) = ''
,@column_list NVARCHAR(MAX) = ''
,@fill_table NVARCHAR(MAX)
,@sql NVARCHAR(MAX) = ''
,@wholeshebang NVARCHAR(MAX) = ''
,@col_count INT
,@id_object INT
,@temp_table_name NVARCHAR(256) = '##$@table_name$_' + CAST(@@SPID AS NVARCHAR(8))
,@table_name NVARCHAR(256) = '$@table_name$'
,@method NVARCHAR(256) = '$@method$'

IF OBJECT_ID('tempdb..#$@table_name$_' + CAST(@@SPID AS NVARCHAR(8))) IS NOT NULL
EXEC ('DROP TABLE ' + @temp_table_name)

SET @wholeshebang = '$CURRENT(selection, escape_quotes)$'

SET @wholeshebang = CASE
WHEN CHARINDEX('--4b' ,@wholeshebang) = 0
AND CHARINDEX('--4e' ,@wholeshebang) = 0 THEN '--4b' + CHAR(10) + @wholeshebang + CHAR(10) + '--4e'
WHEN CHARINDEX('--4b' ,@wholeshebang) = 0 THEN '--4b' + CHAR(10) + @wholeshebang
WHEN CHARINDEX('--4e' ,@wholeshebang) = 0 THEN @wholeshebang + CHAR(10) + '--4e'
ELSE @wholeshebang
END

DECLARE @stmt NVARCHAR(MAX) =
REPLACE(REPLACE(@wholeshebang ,'--4b' ,'SELECT * INTO ' + @temp_table_name + ' FROM ( ' + CHAR(10)) ,'--4e' ,') as ganyolas WHERE 1 = 0' + CHAR(10))
/*
SELECT
*
--INTO #TestTableT
FROM
OPENROWSET('SQLNCLI' ,'Server=localhost;Trusted_Connection=yes;' ,'EXECUTE dbo.usp_get_map_nyomvonal_tetelek 1')
*/
EXEC (@stmt)

SET @id_object = OBJECT_ID('tempdb..' + @temp_table_name)

SELECT
@col_count = COUNT(1)
FROM
tempdb.sys.all_columns AS ac
WHERE 1 = 1
AND ac.[object_id] = @id_object

SET @columns = CASE
WHEN @method IN ('CTE' ,'Table variable' ,'Temporary table') THEN (
SELECT
CHAR(9)
+ CASE
WHEN ac.column_id = 1 THEN ''
ELSE ','
END
+ ac.[name]
+ ' '
+ UPPER(TYPE_NAME(ac.system_type_id))
+ CASE
WHEN TYPE_NAME(ac.system_type_id) IN ('decimal' ,'numeric') THEN N'(' + CAST(ac.[precision] AS NVARCHAR(256)) + N', ' +
CAST(ac.scale AS NVARCHAR(256)) + N')'
WHEN TYPE_NAME(ac.system_type_id) IN ('nvarchar' ,'varchar')
AND ac.max_length = -1 THEN N'(MAX)'
WHEN TYPE_NAME(ac.system_type_id) IN ('char' ,'varchar' ,'varbinary') THEN N'(' + CAST(ac.max_length AS NVARCHAR(256)) + N')'
WHEN TYPE_NAME(ac.system_type_id) IN ('nvarchar') THEN N'(' + CAST(ac.max_length / 2 AS NVARCHAR(256)) + N')'
WHEN TYPE_NAME(ac.system_type_id) IN ('datetime2') THEN N'(' + CAST(ac.scale AS NVARCHAR(256)) + N')'
ELSE ''
END
+ ' '
+ CASE
WHEN ac.is_nullable = 0 THEN 'NOT NULL'
ELSE 'NULL'
END
+ ' '
+ CASE
WHEN ac.column_id </th> 1 THEN ','
ELSE ''
END
-- + 'CHAR(10)'
FROM
tempdb.sys.all_columns AS ac
WHERE ac.[object_id] = @id_object
ORDER BY

ac.column_id
FOR XML PATH(''), TYPE
).value('(./text()[1])' ,'nvarchar(max)')
ELSE '???'
END;

--SET @id_object = OBJECT_ID('dbo.$@table_name$')

IF @method = 'CTE'
BEGIN
SET @header = ';WITH cte_' + @table_name + '( ' + @column_list + ' )
AS ('
SET @footer = ')'

SET @sql = REPLACE(REPLACE(@wholeshebang ,'--4b' ,@header) ,'--4e' ,@footer)
END
ELSE
IF @method = 'Temporary table'
BEGIN
SET @header = 'IF OBJECT_ID(''tempdb..#' + @table_name + ''') IS NOT NULL' + CHAR(10) + ' DROP TABLE #' + @table_name + CHAR(10) + 'CREATE TABLE #' + @table_name + CHAR(10) + '('
SET @footer = ');'

SET @fill_table = 'INSERT INTO #' + @table_name + '(' + @column_list + ' )' + CHAR(10)

SET @sql = ''
+ @header + CHAR(10)
+ @columns + CHAR(10)
+ @footer + CHAR(10)
+ @fill_table + CHAR(10)

SET @sql = REPLACE(REPLACE(@wholeshebang ,'--4b' ,@sql) ,'--4e' ,'')
END
ELSE
IF @method = 'Table variable'
BEGIN
SET @header = 'DECLARE @' + @table_name + ' TABLE('
SET @footer = ');'

SET @fill_table = 'INSERT INTO @' + @table_name + '(' + @column_list + ' )' + CHAR(10)

SET @sql = ''
+ @header + CHAR(10)
+ @columns + CHAR(10)
+ @footer + CHAR(10)
+ @fill_table + CHAR(10)

SET @sql = REPLACE(REPLACE(@wholeshebang ,'--4b' ,@sql) ,'--4e' ,'');
END
ELSE
IF @method = 'Send mail'
BEGIN
SET @sql =N'
--DECLARE @table_html NVARCHAR(MAX) = '''';
SET @table_html = @table_html + CHAR(10) +
N''<H1><<header>></H1>'' + CHAR(10) +
N''<table>'' + CHAR(10) +
N''' + @columns + N''' + CHAR(10) + CHAR(10) +
CAST((SELECT ' + CHAR(10) + @column_list + ' FROM ' + '(' + REPLACE(REPLACE(@wholeshebang ,'--4b' ,'') ,'--4e' ,'') + N'
) AS html FOR XML PATH(''tr''), TYPE) AS NVARCHAR(MAX))
+ CHAR(10) + N''</table>'';';
END

IF OBJECT_ID('tempdb..' + @temp_table_name) IS NOT NULL
EXEC ('DROP TABLE ' + @temp_table_name)

SELECT
@sql + CHAR(10)
--$$