create TVC ("Table Value Constructor") script from temp table (same like SSMSBoost provides select script )

Question

My requirement is I need to create data select script from table/or complex query result. (same like SSMSBoost provides to generate select script from query) Here I have developed logic for it in TSQL.

OR in other word, what ever result is displayed in DATAGRID OF SSMS, I wan't that data as a select script.

Example : below is the result of any query

from above result I want to generate it's select script as below.

SELECT * FROM ( VALUES 
(256087,'MCD','217647-A33')
,(256526,'MCD','217647-A33')
,(256777,'HARP','217647-A33')
,(256794,'MCD','217647-A33')
,(256835,'MCD','217647-A33')
 ) VTE
([ID],[prod],[provn&qw])

---

In SSMSBOOST if you right click on result datagrid and click on select script than it will generate such script for you. Same thing I want to do without SSMS Boost.

My Working code : save your query result into temp table, assign it to @TBL and run it will generate result.

I want to do this thing without saving data into temp table same as SSMSBoost. Let me know if you have any more ideas.

DECLARE @QUERY VARCHAR(MAX);
DECLARE @TBL VARCHAR(20) = '#TEMP1';

;WITH CTE as
(
    SELECT distinct t.name as SYStype, '['+C.name+']' COLNAME, C.max_length , column_id
    FROM tempdb.sys.columns C
    INNER JOIN sys.types T on T.system_type_id = C.system_type_id
    WHERE [object_id] = OBJECT_ID(N'tempdb..'+@TBL)
    AND t.name <> 'sysname'
)
,LINES AS
( --CREATES SINGLE LINES 
SELECT distinct LN = concat('SELECT CONCAT('',('',', SUBSTRING((SELECT ','','',' + 
    CASE WHEN SYStype in('nvarchar','varchar','datetime','time','date','uniqueidentifier') THEN CONCAT('CASE WHEN ',COLNAME,' IS NULL THEN ''NULL'' ELSE CONCAT('''''''',',' REPLACE(' ,COLNAME,','''''''','''''''''''')',','''''''') END') 
    ELSE  CONCAT('CASE WHEN ',COLNAME,' IS NULL THEN ''NULL'' ELSE CAST(',COLNAME,' AS VARCHAR(20)) END') END +''
    --ELSE COLNAME END 
FROM CTE
order by column_id FOR XML path (''), TYPE).value('.[1]', 'NVARCHAR(MAX)'),6,90000),','')'')',CHAR(10),' FROM '+@TBL)
)
select @QUERY=
concat('SELECT ''SELECT * FROM ( VALUES ''',CHAR(10),'UNION ALL',CHAR(10),
 LN 
,CHAR(10),'UNION ALL',CHAR(10),'SELECT '' ) VTE', CHAR(10),'(',
(SELECT SUBSTRING((SELECT ','+COLNAME FROM  CTE order by column_id FOR XML path (''), TYPE).value('.[1]', 'NVARCHAR(MAX)'),2,2000)) ,')''')
FROM LINES

EXECUTE (@QUERY);

Answer 1

Finally here is the query which can generate Transact-SQL table value constructor from any temp table

1. just replace #Temp with your temp table name
2. Optional you can provide column for order by

here is the final query

DECLARE @QUERY VARCHAR(MAX);
DECLARE @TBL VARCHAR(20) = '#TEMP';
DECLARE @ORDERBYCOL VARCHAR(20) = '1';--Optional replace 1 with your column in which order  by    

    ;WITH CTE as
    (
        SELECT distinct t.name as SYStype, '['+C.name+']' COLNAME, C.max_length , column_id
        FROM tempdb.sys.columns C
        INNER JOIN sys.types T on T.system_type_id = C.system_type_id
        WHERE [object_id] = OBJECT_ID(N'tempdb..'+@TBL)
        AND t.name <> 'sysname'
    )
    ,LINES AS
    (
        SELECT distinct LN = concat('SELECT CONCAT(CASE WHEN _ROW_NUM_=1 THEN '' '' ELSE '','' END, ''('','
            , SUBSTRING(
                    (SELECT ','','',' + 
                        CASE WHEN SYStype in('nvarchar','varchar','datetime','time','date','uniqueidentifier') THEN CONCAT('CASE WHEN ',COLNAME,' IS NULL THEN ''NULL'' ELSE CONCAT('''''''',',' REPLACE(' ,COLNAME,','''''''','''''''''''')',','''''''') END') 
                        ELSE  CONCAT('CASE WHEN ',COLNAME,' IS NULL THEN ''NULL'' ELSE CAST(',COLNAME,' AS VARCHAR(20)) END') END +''
                        --ELSE COLNAME END 
                    FROM CTE
                    order by column_id FOR XML path (''), TYPE)
                    .value('.[1]', 'NVARCHAR(MAX)'),6,90000)
        ,','')'')',CHAR(10),' FROM ',
        '(
            SELECT TOP 1000000 * , _ROW_NUM_ = ROW_NUMBER() OVER (ORDER BY (SELECT 1) )
            FROM ',@TBL,' ORDER BY ',@ORDERBYCOL,'
        ) CTE ' )
    )
    select @QUERY=
    concat('SELECT ''SELECT * FROM ( VALUES ''',CHAR(10),'UNION ALL',CHAR(10),
     LN 
    ,CHAR(10),'UNION ALL',CHAR(10),'SELECT '' ) VTE', CHAR(10),'(',
    (SELECT SUBSTRING((SELECT ','+COLNAME FROM  CTE order by column_id FOR XML path (''), TYPE).value('.[1]', 'NVARCHAR(MAX)'),2,2000)) ,')''')
    FROM LINES
    
    EXECUTE (@QUERY);