SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
SELECT query to table var/temp table/CTE (SQL Server 2k8)

 
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository View previous topic
View next topic
SELECT query to table var/temp table/CTE (SQL Server 2k8)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1395

Post SELECT query to table var/temp table/CTE (SQL Server 2k8) Reply with quote
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$

Tue Jul 05, 2011 4:01 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1395

Post Reply with quote
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)
--$$

Mon Nov 19, 2012 7:34 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.