It is possible to create a dynamic table from a result set ?

  • Hi guys,

    I need help for store in a table #temp result set of a function PIVOT dynamics and join others data later, for example:

    -- Declare variable
    DECLARE    @query    VARCHAR(MAX)

    -- Doing Dinamic Column
    SET@query=''
    SELECT@query=@query+'['+[a].[Column]+'], '
    FROM
      (SELECT DISTINCT
        CONVERT(VARCHAR(2),
        NomeTamanho) AS [Column]
      FROM Tamanhos
      )AS a
    SET @query = LEFT(@query,len(@query)-1)

    -- Doing query final with PIVOT
    SET @query ='SELECT
            Descricao,
            CodProduto, '
            +@query
         +' FROM (SELECT P.Descricao, P.CodProduto, T.NomeTamanho, SUM(IP.Quant) QTDETOTAL '+
         '   FROM Produtos P, GradeProdutos GP, Tamanhos T, ItensPedidos IP '+
             ' WHERE P.CodProduto = GP.CodProduto '+
             ' AND P.CodProduto = IP.CodProduto '+
             ' AND GP.CodTamanho = T.CodTamanho '+
           ' AND IP.CodTamanho = T.CodTamanho '+
           ' AND SUBSTRING(P.CodProduto, 1,2 ) = ''CL'' '+
           ' AND SUBSTRING(P.CodProduto, 5,3 ) = ''053'' '+
           ' AND YEAR(P.DATA) >= 2012 '+  
             ' GROUP BY P.CodProduto, P.Descricao, T.NomeTamanho '+') sq
            PIVOT (SUM(QTDETOTAL) FOR NomeTamanho IN('+@query+')) AS pt'
    PRINT @query
    EXEC (@query)

    The variable @query return data I need store in #temp table for join others data later.

    Thank you.

  • Wrote a function for this a while back, you might find it helpful.
    😎
    Details in the comments.

    USE TEEST;
    GO
    SET NOCOUNT ON;

    GO
    CREATE OR ALTER FUNCTION dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
    (
      @TABLE_NAME   NVARCHAR(128)
     ,@SCHEMA_NAME   NVARCHAR(128)
     ,@EXEC_SQL    NVARCHAR(MAX)
     ,@PARAMETER_STR  NVARCHAR(MAX)
     ,@IS_TABLE_VARIABLE BIT
     ,@IS_TEMPORARY_TABLE BIT
     ,@CREATE_INSERT_DML TINYINT
    )
    RETURNS TABLE
    AS
    /******************************************************************************
    -------------------------------------------------------------------------------
    OBJECT NAME: dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
    -------------------------------------------------------------------------------
    OBJECT VERSION: 1.7.43287
    -------------------------------------------------------------------------------
    OUTPUT: TABLE_DDL NVARCHAR(MAX)
    -------------------------------------------------------------------------------
    PLATFORM: SQL SERVER 2012 AND LATER (ALL EDITIONS)
    -------------------------------------------------------------------------------
    PARAMETERS:
    NAME      Data type  Desciption
    @TABLE_NAME   NVARCHAR(128) The name of the target table

    @SCHEMA_NAME   NVARCHAR(128) Target schema name

    @EXEC_SQL    NVARCHAR(MAX) A valid sql statement that returns a result
                set. The user must have permissions to run
                the statement, otherwise the function will
                not return any output.

    @PARAMETER_STR  NVARCHAR(MAX) Parameter(s) for the sql statement in the
                same format as for the sp_executesql system
                procedure.

    @IS_TABLE_VARIABLE BIT    Generate table variable DDL
                0 The function does not return a table
                 variable DDL
                1 The function returns a table variable DDL
                 and prefixes the name passed in the
                 @TABLE_NAME with "@"

    @IS_TEMPORARY_TABLE BIT    Generate temporary table DDL

    @CREATE_INSERT_DML TINYINT   Appends an insert statement to the output
                0 No insert statement generated
                1 Appends an insert statement to the output
                2 Appends an insert statement to the output
                 and adds the statement passed in the
                 @EXEC_SQL parameter to the output.
    -------------------------------------------------------------------------------
    AUTHOR: Eirikur Eiriksson
       http://www.eesql.com
    -------------------------------------------------------------------------------
    This code is provided as is, without any warranty. It is free to use for any
    legitimate purposes, given that the whole and unedited comment section is
    included.
    -------------------------------------------------------------------------------
    COPYRIGHT © 2012 - 2018 Eirikur Eiriksson
    -------------------------------------------------------------------------------
    DESCRIPTION:
    -------------------------------------------------------------------------------
    The function returns a DDL for any executable expression that returns a result
    set with named columns. Columns with no name (No column name) will be ignored
    and will not be present in the generated DDL.

    The function can generate all three types of table DDL by setting the
    @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters:

    DDL Type      @IS_TABLE_VARIABLE @IS_TEMPORARY_TABLE
    ----------------------------------------------------------------
    create table     0       0
    create temporary table 0       1
    declare table variable 1       0
    NULL       1       1
    ----------------------------------------------------------------

    -------------------------------------------------------------------------------
    NOTE:
    -------------------------------------------------------------------------------
    1) If both @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters are set to 1
     then the function will return an empty result set (NULL).

    2) The DDL will always be HEAP.

    3) The column nullability is the only constraint in the DDL

    4) The function will return an empty result set (NULL) if any query profiling
     options are enabled:
       a) STATISTICS PROFILE
       b) STATISTICS XML
       c) SHOWPLAN_ALL
       d) SHOWPLAN_TEXT
       e) SHOWPLAN_XML
       f) "Include Actual Execution Plan" (SSMS)
    -------------------------------------------------------------------------------
    USAGE:
    -------------------------------------------------------------------------------
    The function is an alternative and enhancement to SET FMTONLY which has been
    deprecated and OPENROWSET which requires enabeling the
    "Ad Hoc Distributed Queries" configuration.

    -------------------------------------------------------------------------------
    VERSION HISTORY:
    -------------------------------------------------------------------------------
    1.0 Initial coding
    1.1 Added table variable option
    1.2 Added temporary table option
    1.3 Output changed to NULL for invalid table type options
    1.4 Minor changes in output formst
      Added usage examples to the comment section
    1.5 Added the option of appending an insert statement to the output.
    1.6 Changed the @CREATE_INSERT_DML parameter to TINYINT
      Added the option of appendin ghe statement passed in the @EXEC_SQL
      parameter to the output.
    1.7 Fixed the usage examples.
    -------------------------------------------------------------------------------
    EXAMPLES:
    -------------------------------------------------------------------------------
    1) Create a permanent table (dbo.MY_NEW_TABLE) based on the output of a dynamic
     query:
    -------------------------------------------------------------------------------
    DECLARE @TABLE_NAME   NVARCHAR(128) = N'MY_NEW_TABLE';
    DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
    DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'SELECT object_id FROM sys.objects';
    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 0;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 0;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    SELECT
      X.TABLE_DDL
    FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
                   ,@SCHEMA_NAME
                   ,@EXEC_SQL
                   ,@PARAMETER_STR
                   ,@IS_TABLE_VARIABLE
                   ,@IS_TEMPORARY_TABLE
                   ,@CREATE_INSERT_DML) X

    -------------------------------------------------------------------------------
    2) Capture the output of a stored procedure into a temporary table
     (#TBL_SP_WHO) and select from the table;
    -------------------------------------------------------------------------------
    DECLARE @TABLE_NAME   NVARCHAR(128) = N'TBL_SP_WHO';
    DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
    DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'EXEC sp_who';
    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 0;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 1;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
    (
    SELECT
      X.TABLE_DDL
    FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
                   ,@SCHEMA_NAME
                   ,@EXEC_SQL
                   ,@PARAMETER_STR
                   ,@IS_TABLE_VARIABLE
                   ,@IS_TEMPORARY_TABLE
                   ,@CREATE_INSERT_DML) X
    ) + N'
    INSERT INTO #' + @TABLE_NAME + N'
    ' + @EXEC_SQL + N'

    SELECT * FROM  #' + @TABLE_NAME + NCHAR(59);

    EXEC (@EXEC_SP_WHO_TO_TABLE);

    -------------------------------------------------------------------------------
    3) Capture the result set of a stored procedure into a table variable (@SPWHO)
     and conditionally select from the table variable.

    DECLARE @TABLE_NAME   NVARCHAR(128) = N'SPWHO';
    DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
    DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'EXEC sp_who';
    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 1;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 0;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
    (
    SELECT
      X.TABLE_DDL
    FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
                   ,@SCHEMA_NAME
                   ,@EXEC_SQL
                   ,@PARAMETER_STR
                   ,@IS_TABLE_VARIABLE
                   ,@IS_TEMPORARY_TABLE
                   ,@CREATE_INSERT_DML) X
    ) + N'
    INSERT INTO @' + @TABLE_NAME + N'
    ' + @EXEC_SQL + N'

    SELECT * FROM  @' + @TABLE_NAME + N'
    WHERE status = ''runnable'' ' + NCHAR(59);

    EXEC (@EXEC_SP_WHO_TO_TABLE);

    -------------------------------------------------------------------------------
    4) Generate HEAP DDL for all tables in a database

    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 0;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 0;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    DECLARE @NL      NCHAR(2)   = NCHAR(13) + NCHAR(10);
    DECLARE @QT      NCHAR(2)   = NCHAR(45) + NCHAR(45);
    SELECT
      CONCAT (
       @QT
       ,QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id)) + NCHAR(46) + QUOTENAME(STAB.name)
       ,@NL
       ,X.TABLE_DDL
       ,@NL
       ,@NL
        )
    FROM  sys.tables STAB
    CROSS APPLY dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
    (  STAB.name
      ,OBJECT_SCHEMA_NAME(STAB.object_id)
      ,N'SELECT * FROM '
       + QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id))
       + NCHAR(46)
       + QUOTENAME(STAB.name)
       + NCHAR(59)
      ,@PARAMETER_STR
      ,@IS_TABLE_VARIABLE
      ,@IS_TEMPORARY_TABLE
      ,@CREATE_INSERT_DML
    ) X
    FOR XML PATH(''),TYPE;
    ******************************************************************************/
    RETURN
    SELECT
      CASE
      -- Permanent table
       WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN
        N'CREATE TABLE ' + QUOTENAME(@SCHEMA_NAME)
         + NCHAR(46) + QUOTENAME(@TABLE_NAME)
         + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Temporary table
       WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN
        N'CREATE TABLE ' + NCHAR(35) + @TABLE_NAME
         + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Table variable
       WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN
        N'DECLARE ' + NCHAR(64) + (@TABLE_NAME)
         + N' TABLE ' + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Other options are invalid and the result set is emptied by concatenating
      -- the output with NULL.
       ELSE NULL

      END
    + NCHAR(13) + NCHAR(10) + NCHAR(32) +
    (
      SELECT
       STUFF(
          (
           SELECT
            NCHAR(44) + QUOTENAME(R.name) + N' '
            + (R.system_type_name) + N' '
            + CASE
             WHEN R.is_nullable = 0 THEN N'NOT NULL'
             ELSE N'  NULL'
            END
            + NCHAR(13) + NCHAR(10)
           FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R
           WHERE R.is_hidden = 0
           ORDER BY R.column_ordinal ASC
           FOR XML PATH(''),TYPE
          ).value('(./text())[1]','NVARCHAR(MAX)')
         ,1,1,N'')
    ) + NCHAR(41) + NCHAR(59) +
    CASE
      WHEN @CREATE_INSERT_DML > 0 THEN
          CASE
        -- Permanent table
          WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN
           N'INSERT INTO ' + QUOTENAME(@SCHEMA_NAME)
            + NCHAR(46) + QUOTENAME(@TABLE_NAME)
            + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

        -- Temporary table
          WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN
           N'INSERT INTO ' + NCHAR(35) + @TABLE_NAME
            + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

        -- Table variable
          WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN
           N'INSERT INTO ' + NCHAR(64) + (@TABLE_NAME)
            + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

        -- Other options are invalid and the result set is emptied by concatenating
        -- the output with NULL.
          ELSE NULL

        END
       + NCHAR(13) + NCHAR(10) + NCHAR(32) +
       (
        SELECT
          STUFF(
            (
              SELECT
               NCHAR(44) + QUOTENAME(R.name) + N' '
              + NCHAR(13) + NCHAR(10)
              FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R
              WHERE R.is_hidden = 0
              ORDER BY R.column_ordinal ASC
              FOR XML PATH(''),TYPE
            ).value('(./text())[1]','NVARCHAR(MAX)')
           ,1,1,N'')
       ) + NCHAR(41)
      ELSE N''
    END

    AS TABLE_DDL
    ;

  • Pretty cool, Eirikur.  And, "Look Ma!!!!", it's fully documented!  This thread is now a part of my briefcase!

    You should write an article about this bad boy. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Sunday, July 8, 2018 9:11 AM

    Pretty cool, Eirikur.  And, "Look Ma!!!!", it's fully documented!  This thread is now a part of my briefcase!

    You should write an article about this bad boy. 😉

    Thanks Jeff, it is a nifty little thing that has saved me many many hours of work. I'm happy to share and since you mentioned it, I will dig up the internal article I wrote at the time and publish it here.
    😎

  • Eirikur Eiriksson - Sunday, July 8, 2018 9:28 AM

    Jeff Moden - Sunday, July 8, 2018 9:11 AM

    Pretty cool, Eirikur.  And, "Look Ma!!!!", it's fully documented!  This thread is now a part of my briefcase!

    You should write an article about this bad boy. 😉

    Thanks Jeff, it is a nifty little thing that has saved me many many hours of work. I'm happy to share and since you mentioned it, I will dig up the internal article I wrote at the time and publish it here.
    😎

    Sounds great.  Thanks for the treat.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Eirikur Eiriksson - Sunday, July 8, 2018 1:49 AM

    Wrote a function for this a while back, you might find it helpful.
    😎
    Details in the comments.

    USE TEEST;
    GO
    SET NOCOUNT ON;

    GO
    CREATE OR ALTER FUNCTION dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
    (
      @TABLE_NAME   NVARCHAR(128)
     ,@SCHEMA_NAME   NVARCHAR(128)
     ,@EXEC_SQL    NVARCHAR(MAX)
     ,@PARAMETER_STR  NVARCHAR(MAX)
     ,@IS_TABLE_VARIABLE BIT
     ,@IS_TEMPORARY_TABLE BIT
     ,@CREATE_INSERT_DML TINYINT
    )
    RETURNS TABLE
    AS
    /******************************************************************************
    -------------------------------------------------------------------------------
    OBJECT NAME: dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
    -------------------------------------------------------------------------------
    OBJECT VERSION: 1.7.43287
    -------------------------------------------------------------------------------
    OUTPUT: TABLE_DDL NVARCHAR(MAX)
    -------------------------------------------------------------------------------
    PLATFORM: SQL SERVER 2012 AND LATER (ALL EDITIONS)
    -------------------------------------------------------------------------------
    PARAMETERS:
    NAME      Data type  Desciption
    @TABLE_NAME   NVARCHAR(128) The name of the target table

    @SCHEMA_NAME   NVARCHAR(128) Target schema name

    @EXEC_SQL    NVARCHAR(MAX) A valid sql statement that returns a result
                set. The user must have permissions to run
                the statement, otherwise the function will
                not return any output.

    @PARAMETER_STR  NVARCHAR(MAX) Parameter(s) for the sql statement in the
                same format as for the sp_executesql system
                procedure.

    @IS_TABLE_VARIABLE BIT    Generate table variable DDL
                0 The function does not return a table
                 variable DDL
                1 The function returns a table variable DDL
                 and prefixes the name passed in the
                 @TABLE_NAME with "@"

    @IS_TEMPORARY_TABLE BIT    Generate temporary table DDL

    @CREATE_INSERT_DML TINYINT   Appends an insert statement to the output
                0 No insert statement generated
                1 Appends an insert statement to the output
                2 Appends an insert statement to the output
                 and adds the statement passed in the
                 @EXEC_SQL parameter to the output.
    -------------------------------------------------------------------------------
    AUTHOR: Eirikur Eiriksson
       http://www.eesql.com
    -------------------------------------------------------------------------------
    This code is provided as is, without any warranty. It is free to use for any
    legitimate purposes, given that the whole and unedited comment section is
    included.
    -------------------------------------------------------------------------------
    COPYRIGHT © 2012 - 2018 Eirikur Eiriksson
    -------------------------------------------------------------------------------
    DESCRIPTION:
    -------------------------------------------------------------------------------
    The function returns a DDL for any executable expression that returns a result
    set with named columns. Columns with no name (No column name) will be ignored
    and will not be present in the generated DDL.

    The function can generate all three types of table DDL by setting the
    @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters:

    DDL Type      @IS_TABLE_VARIABLE @IS_TEMPORARY_TABLE
    ----------------------------------------------------------------
    create table     0       0
    create temporary table 0       1
    declare table variable 1       0
    NULL       1       1
    ----------------------------------------------------------------

    -------------------------------------------------------------------------------
    NOTE:
    -------------------------------------------------------------------------------
    1) If both @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters are set to 1
     then the function will return an empty result set (NULL).

    2) The DDL will always be HEAP.

    3) The column nullability is the only constraint in the DDL

    4) The function will return an empty result set (NULL) if any query profiling
     options are enabled:
       a) STATISTICS PROFILE
       b) STATISTICS XML
       c) SHOWPLAN_ALL
       d) SHOWPLAN_TEXT
       e) SHOWPLAN_XML
       f) "Include Actual Execution Plan" (SSMS)
    -------------------------------------------------------------------------------
    USAGE:
    -------------------------------------------------------------------------------
    The function is an alternative and enhancement to SET FMTONLY which has been
    deprecated and OPENROWSET which requires enabeling the
    "Ad Hoc Distributed Queries" configuration.

    -------------------------------------------------------------------------------
    VERSION HISTORY:
    -------------------------------------------------------------------------------
    1.0 Initial coding
    1.1 Added table variable option
    1.2 Added temporary table option
    1.3 Output changed to NULL for invalid table type options
    1.4 Minor changes in output formst
      Added usage examples to the comment section
    1.5 Added the option of appending an insert statement to the output.
    1.6 Changed the @CREATE_INSERT_DML parameter to TINYINT
      Added the option of appendin ghe statement passed in the @EXEC_SQL
      parameter to the output.
    1.7 Fixed the usage examples.
    -------------------------------------------------------------------------------
    EXAMPLES:
    -------------------------------------------------------------------------------
    1) Create a permanent table (dbo.MY_NEW_TABLE) based on the output of a dynamic
     query:
    -------------------------------------------------------------------------------
    DECLARE @TABLE_NAME   NVARCHAR(128) = N'MY_NEW_TABLE';
    DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
    DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'SELECT object_id FROM sys.objects';
    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 0;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 0;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    SELECT
      X.TABLE_DDL
    FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
                   ,@SCHEMA_NAME
                   ,@EXEC_SQL
                   ,@PARAMETER_STR
                   ,@IS_TABLE_VARIABLE
                   ,@IS_TEMPORARY_TABLE
                   ,@CREATE_INSERT_DML) X

    -------------------------------------------------------------------------------
    2) Capture the output of a stored procedure into a temporary table
     (#TBL_SP_WHO) and select from the table;
    -------------------------------------------------------------------------------
    DECLARE @TABLE_NAME   NVARCHAR(128) = N'TBL_SP_WHO';
    DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
    DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'EXEC sp_who';
    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 0;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 1;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
    (
    SELECT
      X.TABLE_DDL
    FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
                   ,@SCHEMA_NAME
                   ,@EXEC_SQL
                   ,@PARAMETER_STR
                   ,@IS_TABLE_VARIABLE
                   ,@IS_TEMPORARY_TABLE
                   ,@CREATE_INSERT_DML) X
    ) + N'
    INSERT INTO #' + @TABLE_NAME + N'
    ' + @EXEC_SQL + N'

    SELECT * FROM  #' + @TABLE_NAME + NCHAR(59);

    EXEC (@EXEC_SP_WHO_TO_TABLE);

    -------------------------------------------------------------------------------
    3) Capture the result set of a stored procedure into a table variable (@SPWHO)
     and conditionally select from the table variable.

    DECLARE @TABLE_NAME   NVARCHAR(128) = N'SPWHO';
    DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
    DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'EXEC sp_who';
    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 1;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 0;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
    (
    SELECT
      X.TABLE_DDL
    FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
                   ,@SCHEMA_NAME
                   ,@EXEC_SQL
                   ,@PARAMETER_STR
                   ,@IS_TABLE_VARIABLE
                   ,@IS_TEMPORARY_TABLE
                   ,@CREATE_INSERT_DML) X
    ) + N'
    INSERT INTO @' + @TABLE_NAME + N'
    ' + @EXEC_SQL + N'

    SELECT * FROM  @' + @TABLE_NAME + N'
    WHERE status = ''runnable'' ' + NCHAR(59);

    EXEC (@EXEC_SP_WHO_TO_TABLE);

    -------------------------------------------------------------------------------
    4) Generate HEAP DDL for all tables in a database

    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 0;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 0;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    DECLARE @NL      NCHAR(2)   = NCHAR(13) + NCHAR(10);
    DECLARE @QT      NCHAR(2)   = NCHAR(45) + NCHAR(45);
    SELECT
      CONCAT (
       @QT
       ,QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id)) + NCHAR(46) + QUOTENAME(STAB.name)
       ,@NL
       ,X.TABLE_DDL
       ,@NL
       ,@NL
        )
    FROM  sys.tables STAB
    CROSS APPLY dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
    (  STAB.name
      ,OBJECT_SCHEMA_NAME(STAB.object_id)
      ,N'SELECT * FROM '
       + QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id))
       + NCHAR(46)
       + QUOTENAME(STAB.name)
       + NCHAR(59)
      ,@PARAMETER_STR
      ,@IS_TABLE_VARIABLE
      ,@IS_TEMPORARY_TABLE
      ,@CREATE_INSERT_DML
    ) X
    FOR XML PATH(''),TYPE;
    ******************************************************************************/
    RETURN
    SELECT
      CASE
      -- Permanent table
       WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN
        N'CREATE TABLE ' + QUOTENAME(@SCHEMA_NAME)
         + NCHAR(46) + QUOTENAME(@TABLE_NAME)
         + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Temporary table
       WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN
        N'CREATE TABLE ' + NCHAR(35) + @TABLE_NAME
         + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Table variable
       WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN
        N'DECLARE ' + NCHAR(64) + (@TABLE_NAME)
         + N' TABLE ' + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Other options are invalid and the result set is emptied by concatenating
      -- the output with NULL.
       ELSE NULL

      END
    + NCHAR(13) + NCHAR(10) + NCHAR(32) +
    (
      SELECT
       STUFF(
          (
           SELECT
            NCHAR(44) + QUOTENAME(R.name) + N' '
            + (R.system_type_name) + N' '
            + CASE
             WHEN R.is_nullable = 0 THEN N'NOT NULL'
             ELSE N'  NULL'
            END
            + NCHAR(13) + NCHAR(10)
           FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R
           WHERE R.is_hidden = 0
           ORDER BY R.column_ordinal ASC
           FOR XML PATH(''),TYPE
          ).value('(./text())[1]','NVARCHAR(MAX)')
         ,1,1,N'')
    ) + NCHAR(41) + NCHAR(59) +
    CASE
      WHEN @CREATE_INSERT_DML > 0 THEN
          CASE
        -- Permanent table
          WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN
           N'INSERT INTO ' + QUOTENAME(@SCHEMA_NAME)
            + NCHAR(46) + QUOTENAME(@TABLE_NAME)
            + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

        -- Temporary table
          WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN
           N'INSERT INTO ' + NCHAR(35) + @TABLE_NAME
            + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

        -- Table variable
          WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN
           N'INSERT INTO ' + NCHAR(64) + (@TABLE_NAME)
            + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

        -- Other options are invalid and the result set is emptied by concatenating
        -- the output with NULL.
          ELSE NULL

        END
       + NCHAR(13) + NCHAR(10) + NCHAR(32) +
       (
        SELECT
          STUFF(
            (
              SELECT
               NCHAR(44) + QUOTENAME(R.name) + N' '
              + NCHAR(13) + NCHAR(10)
              FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R
              WHERE R.is_hidden = 0
              ORDER BY R.column_ordinal ASC
              FOR XML PATH(''),TYPE
            ).value('(./text())[1]','NVARCHAR(MAX)')
           ,1,1,N'')
       ) + NCHAR(41)
      ELSE N''
    END

    AS TABLE_DDL
    ;

    Eirikur Eiriksson - Sunday, July 8, 2018 1:49 AM

    Wrote a function for this a while back, you might find it helpful.
    😎
    Details in the comments.

    USE TEEST;
    GO
    SET NOCOUNT ON;

    GO
    CREATE OR ALTER FUNCTION dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
    (
      @TABLE_NAME   NVARCHAR(128)
     ,@SCHEMA_NAME   NVARCHAR(128)
     ,@EXEC_SQL    NVARCHAR(MAX)
     ,@PARAMETER_STR  NVARCHAR(MAX)
     ,@IS_TABLE_VARIABLE BIT
     ,@IS_TEMPORARY_TABLE BIT
     ,@CREATE_INSERT_DML TINYINT
    )
    RETURNS TABLE
    AS
    /******************************************************************************
    -------------------------------------------------------------------------------
    OBJECT NAME: dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
    -------------------------------------------------------------------------------
    OBJECT VERSION: 1.7.43287
    -------------------------------------------------------------------------------
    OUTPUT: TABLE_DDL NVARCHAR(MAX)
    -------------------------------------------------------------------------------
    PLATFORM: SQL SERVER 2012 AND LATER (ALL EDITIONS)
    -------------------------------------------------------------------------------
    PARAMETERS:
    NAME      Data type  Desciption
    @TABLE_NAME   NVARCHAR(128) The name of the target table

    @SCHEMA_NAME   NVARCHAR(128) Target schema name

    @EXEC_SQL    NVARCHAR(MAX) A valid sql statement that returns a result
                set. The user must have permissions to run
                the statement, otherwise the function will
                not return any output.

    @PARAMETER_STR  NVARCHAR(MAX) Parameter(s) for the sql statement in the
                same format as for the sp_executesql system
                procedure.

    @IS_TABLE_VARIABLE BIT    Generate table variable DDL
                0 The function does not return a table
                 variable DDL
                1 The function returns a table variable DDL
                 and prefixes the name passed in the
                 @TABLE_NAME with "@"

    @IS_TEMPORARY_TABLE BIT    Generate temporary table DDL

    @CREATE_INSERT_DML TINYINT   Appends an insert statement to the output
                0 No insert statement generated
                1 Appends an insert statement to the output
                2 Appends an insert statement to the output
                 and adds the statement passed in the
                 @EXEC_SQL parameter to the output.
    -------------------------------------------------------------------------------
    AUTHOR: Eirikur Eiriksson
       http://www.eesql.com
    -------------------------------------------------------------------------------
    This code is provided as is, without any warranty. It is free to use for any
    legitimate purposes, given that the whole and unedited comment section is
    included.
    -------------------------------------------------------------------------------
    COPYRIGHT © 2012 - 2018 Eirikur Eiriksson
    -------------------------------------------------------------------------------
    DESCRIPTION:
    -------------------------------------------------------------------------------
    The function returns a DDL for any executable expression that returns a result
    set with named columns. Columns with no name (No column name) will be ignored
    and will not be present in the generated DDL.

    The function can generate all three types of table DDL by setting the
    @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters:

    DDL Type      @IS_TABLE_VARIABLE @IS_TEMPORARY_TABLE
    ----------------------------------------------------------------
    create table     0       0
    create temporary table 0       1
    declare table variable 1       0
    NULL       1       1
    ----------------------------------------------------------------

    -------------------------------------------------------------------------------
    NOTE:
    -------------------------------------------------------------------------------
    1) If both @IS_TABLE_VARIABLE and @IS_TEMPORARY_TABLE parameters are set to 1
     then the function will return an empty result set (NULL).

    2) The DDL will always be HEAP.

    3) The column nullability is the only constraint in the DDL

    4) The function will return an empty result set (NULL) if any query profiling
     options are enabled:
       a) STATISTICS PROFILE
       b) STATISTICS XML
       c) SHOWPLAN_ALL
       d) SHOWPLAN_TEXT
       e) SHOWPLAN_XML
       f) "Include Actual Execution Plan" (SSMS)
    -------------------------------------------------------------------------------
    USAGE:
    -------------------------------------------------------------------------------
    The function is an alternative and enhancement to SET FMTONLY which has been
    deprecated and OPENROWSET which requires enabeling the
    "Ad Hoc Distributed Queries" configuration.

    -------------------------------------------------------------------------------
    VERSION HISTORY:
    -------------------------------------------------------------------------------
    1.0 Initial coding
    1.1 Added table variable option
    1.2 Added temporary table option
    1.3 Output changed to NULL for invalid table type options
    1.4 Minor changes in output formst
      Added usage examples to the comment section
    1.5 Added the option of appending an insert statement to the output.
    1.6 Changed the @CREATE_INSERT_DML parameter to TINYINT
      Added the option of appendin ghe statement passed in the @EXEC_SQL
      parameter to the output.
    1.7 Fixed the usage examples.
    -------------------------------------------------------------------------------
    EXAMPLES:
    -------------------------------------------------------------------------------
    1) Create a permanent table (dbo.MY_NEW_TABLE) based on the output of a dynamic
     query:
    -------------------------------------------------------------------------------
    DECLARE @TABLE_NAME   NVARCHAR(128) = N'MY_NEW_TABLE';
    DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
    DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'SELECT object_id FROM sys.objects';
    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 0;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 0;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    SELECT
      X.TABLE_DDL
    FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
                   ,@SCHEMA_NAME
                   ,@EXEC_SQL
                   ,@PARAMETER_STR
                   ,@IS_TABLE_VARIABLE
                   ,@IS_TEMPORARY_TABLE
                   ,@CREATE_INSERT_DML) X

    -------------------------------------------------------------------------------
    2) Capture the output of a stored procedure into a temporary table
     (#TBL_SP_WHO) and select from the table;
    -------------------------------------------------------------------------------
    DECLARE @TABLE_NAME   NVARCHAR(128) = N'TBL_SP_WHO';
    DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
    DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'EXEC sp_who';
    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 0;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 1;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
    (
    SELECT
      X.TABLE_DDL
    FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
                   ,@SCHEMA_NAME
                   ,@EXEC_SQL
                   ,@PARAMETER_STR
                   ,@IS_TABLE_VARIABLE
                   ,@IS_TEMPORARY_TABLE
                   ,@CREATE_INSERT_DML) X
    ) + N'
    INSERT INTO #' + @TABLE_NAME + N'
    ' + @EXEC_SQL + N'

    SELECT * FROM  #' + @TABLE_NAME + NCHAR(59);

    EXEC (@EXEC_SP_WHO_TO_TABLE);

    -------------------------------------------------------------------------------
    3) Capture the result set of a stored procedure into a table variable (@SPWHO)
     and conditionally select from the table variable.

    DECLARE @TABLE_NAME   NVARCHAR(128) = N'SPWHO';
    DECLARE @SCHEMA_NAME   NVARCHAR(128) = N'dbo';
    DECLARE @EXEC_SQL    NVARCHAR(MAX) = N'EXEC sp_who';
    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 1;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 0;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    DECLARE @EXEC_SP_WHO_TO_TABLE NVARCHAR(MAX) =
    (
    SELECT
      X.TABLE_DDL
    FROM dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY( @TABLE_NAME
                   ,@SCHEMA_NAME
                   ,@EXEC_SQL
                   ,@PARAMETER_STR
                   ,@IS_TABLE_VARIABLE
                   ,@IS_TEMPORARY_TABLE
                   ,@CREATE_INSERT_DML) X
    ) + N'
    INSERT INTO @' + @TABLE_NAME + N'
    ' + @EXEC_SQL + N'

    SELECT * FROM  @' + @TABLE_NAME + N'
    WHERE status = ''runnable'' ' + NCHAR(59);

    EXEC (@EXEC_SP_WHO_TO_TABLE);

    -------------------------------------------------------------------------------
    4) Generate HEAP DDL for all tables in a database

    DECLARE @PARAMETER_STR  NVARCHAR(MAX) = NULL;
    DECLARE @IS_TABLE_VARIABLE BIT     = 0;
    DECLARE @IS_TEMPORARY_TABLE BIT     = 0;
    DECLARE @CREATE_INSERT_DML TINYINT   = 0;
    DECLARE @NL      NCHAR(2)   = NCHAR(13) + NCHAR(10);
    DECLARE @QT      NCHAR(2)   = NCHAR(45) + NCHAR(45);
    SELECT
      CONCAT (
       @QT
       ,QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id)) + NCHAR(46) + QUOTENAME(STAB.name)
       ,@NL
       ,X.TABLE_DDL
       ,@NL
       ,@NL
        )
    FROM  sys.tables STAB
    CROSS APPLY dbo.ITVFN_CREATE_TABLE_DDL_FROM_QUERY
    (  STAB.name
      ,OBJECT_SCHEMA_NAME(STAB.object_id)
      ,N'SELECT * FROM '
       + QUOTENAME(OBJECT_SCHEMA_NAME(STAB.object_id))
       + NCHAR(46)
       + QUOTENAME(STAB.name)
       + NCHAR(59)
      ,@PARAMETER_STR
      ,@IS_TABLE_VARIABLE
      ,@IS_TEMPORARY_TABLE
      ,@CREATE_INSERT_DML
    ) X
    FOR XML PATH(''),TYPE;
    ******************************************************************************/
    RETURN
    SELECT
      CASE
      -- Permanent table
       WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN
        N'CREATE TABLE ' + QUOTENAME(@SCHEMA_NAME)
         + NCHAR(46) + QUOTENAME(@TABLE_NAME)
         + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Temporary table
       WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN
        N'CREATE TABLE ' + NCHAR(35) + @TABLE_NAME
         + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Table variable
       WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN
        N'DECLARE ' + NCHAR(64) + (@TABLE_NAME)
         + N' TABLE ' + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

      -- Other options are invalid and the result set is emptied by concatenating
      -- the output with NULL.
       ELSE NULL

      END
    + NCHAR(13) + NCHAR(10) + NCHAR(32) +
    (
      SELECT
       STUFF(
          (
           SELECT
            NCHAR(44) + QUOTENAME(R.name) + N' '
            + (R.system_type_name) + N' '
            + CASE
             WHEN R.is_nullable = 0 THEN N'NOT NULL'
             ELSE N'  NULL'
            END
            + NCHAR(13) + NCHAR(10)
           FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R
           WHERE R.is_hidden = 0
           ORDER BY R.column_ordinal ASC
           FOR XML PATH(''),TYPE
          ).value('(./text())[1]','NVARCHAR(MAX)')
         ,1,1,N'')
    ) + NCHAR(41) + NCHAR(59) +
    CASE
      WHEN @CREATE_INSERT_DML > 0 THEN
          CASE
        -- Permanent table
          WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 0 THEN
           N'INSERT INTO ' + QUOTENAME(@SCHEMA_NAME)
            + NCHAR(46) + QUOTENAME(@TABLE_NAME)
            + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

        -- Temporary table
          WHEN @IS_TABLE_VARIABLE = 0 AND @IS_TEMPORARY_TABLE = 1 THEN
           N'INSERT INTO ' + NCHAR(35) + @TABLE_NAME
            + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

        -- Table variable
          WHEN @IS_TABLE_VARIABLE = 1 AND @IS_TEMPORARY_TABLE = 0 THEN
           N'INSERT INTO ' + NCHAR(64) + (@TABLE_NAME)
            + NCHAR(32) + NCHAR(13) + NCHAR(10) + NCHAR(40)

        -- Other options are invalid and the result set is emptied by concatenating
        -- the output with NULL.
          ELSE NULL

        END
       + NCHAR(13) + NCHAR(10) + NCHAR(32) +
       (
        SELECT
          STUFF(
            (
              SELECT
               NCHAR(44) + QUOTENAME(R.name) + N' '
              + NCHAR(13) + NCHAR(10)
              FROM sys.dm_exec_describe_first_result_set(@EXEC_SQL,@PARAMETER_STR,0) R
              WHERE R.is_hidden = 0
              ORDER BY R.column_ordinal ASC
              FOR XML PATH(''),TYPE
            ).value('(./text())[1]','NVARCHAR(MAX)')
           ,1,1,N'')
       ) + NCHAR(41)
      ELSE N''
    END

    AS TABLE_DDL
    ;

    Eirikur, Thank very much for feedback. I go to work on this now.

  • Eirikur, Thank very much for feedback. I go to work on this now!.

  • antoniop.silv - Monday, July 9, 2018 3:33 PM

    Eirikur, Thank very much for feedback. I go to work on this now!.

    You are very welcome.
    😎

    Feel free to ping back if you have any further questions.

  • Jeff Moden - Sunday, July 8, 2018 2:17 PM

    Eirikur Eiriksson - Sunday, July 8, 2018 9:28 AM

    Jeff Moden - Sunday, July 8, 2018 9:11 AM

    Pretty cool, Eirikur.  And, "Look Ma!!!!", it's fully documented!  This thread is now a part of my briefcase!

    You should write an article about this bad boy. 😉

    Thanks Jeff, it is a nifty little thing that has saved me many many hours of work. I'm happy to share and since you mentioned it, I will dig up the internal article I wrote at the time and publish it here.
    😎

    Sounds great.  Thanks for the treat.

    I was thinking the same thing Jeff. And I stored the code in my toolbox as well. 🙂

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

  • Eirikur Eiriksson - Tuesday, July 10, 2018 12:19 AM

    antoniop.silv - Monday, July 9, 2018 3:33 PM

    Eirikur, Thank very much for feedback. I go to work on this now!.

    You are very welcome.
    😎

    Feel free to ping back if you have any further questions.

    Thank you 😉

  • Eirikur Eiriksson - Tuesday, July 10, 2018 12:19 AM

    antoniop.silv - Monday, July 9, 2018 3:33 PM

    Eirikur, Thank very much for feedback. I go to work on this now!.

    You are very welcome.
    😎

    Feel free to ping back if you have any further questions.

    Eirikur, 

    It's possible to access table #temp outside session, for example:
    FOR TABLE = test

    ...
    INSERT INTO #' + @TABLE_NAME + N'
    ' + @EXEC_SQL + N'
    SELECT * FROM #' + @TABLE_NAME + NCHAR(59);
    EXEC (@EXEC_SP_WHO_TO_TABLE);

    --After session closed
    SELECT * FROM #test

  • antoniop.silv - Wednesday, July 11, 2018 1:00 PM

    Eirikur Eiriksson - Tuesday, July 10, 2018 12:19 AM

    antoniop.silv - Monday, July 9, 2018 3:33 PM

    Eirikur, Thank very much for feedback. I go to work on this now!.

    You are very welcome.
    😎

    Feel free to ping back if you have any further questions.

    Eirikur, 

    It's possible to access table #temp outside session, for example:
    FOR TABLE = test

    ...
    INSERT INTO #' + @TABLE_NAME + N'
    ' + @EXEC_SQL + N'
    SELECT * FROM #' + @TABLE_NAME + NCHAR(59);
    EXEC (@EXEC_SP_WHO_TO_TABLE);

    --After session closed
    SELECT * FROM #test

    Thank you

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply