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:

Code:

$PROMPT(method,Type?,,Use query as cte or variable,variable temporary cte,"Table variable" "Temporary table" CTE)$
$PROMPT(table_name,Name?,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) = ''
,@col_count INT
,@id_object INT
,@table_name NVARCHAR(256) = '$table_name$'

IF OBJECT_ID('tempdb..#$table_name$') IS NOT NULL
DROP TABLE #$table_name$

SELECT
*
INTO #$table_name$
FROM
(
   $CURRENT_SEL$
) AS ganyolas
WHERE 1 = 0

SET @id_object = OBJECT_ID('tempdb..#$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 = (
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 < @col_count THEN CHAR(10)
      ELSE ''
      END
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)')

SET @column_list = (
SELECT
   CASE
      WHEN ac.column_id = 1 THEN ''
      ELSE ','
   END
   + ac.[name]
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)')

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

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

SET @sql = ''
   + @header + CHAR(10)
   + '$CURRENT(selection, escape_quotes)$' + CHAR(10)
   + @footer
END
ELSE
IF '$method$' = 'temporary'
BEGIN
SET @header = '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)
   + '$CURRENT(selection, escape_quotes)$'

END
ELSE
IF '$method$' = '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)
   + '$CURRENT(selection, escape_quotes)$'
END

IF OBJECT_ID('tempdb..#$table_name$') IS NOT NULL
DROP TABLE #$table_name$

SELECT @sql + CHAR(10)
$$