Get column creation datatype for declaration

This is quick way to get a string containing the sql datatype required for a column declaration, a create table, a print variables, or convert data to XML and XML to data, based on temp table and column information.

How to use:

1. Create your select statement
2. Define if you use or not prefix - you have prefix to column names and variable names.
3. Select and run the template to create your snippet code.

ex:

My select:
select top 100 * into #temp FROM [MY_TABLES_VIEWS]

Set prefix to variables : v_
Set prefix to columnes  : c_

Create my data with temp table

-- Delete temp table
BEGIN TRY drop table #temp; END TRY BEGIN CATCH END CATCH;
go
select * into #temp FROM (select top 100 * from myViewAndTable) as tb1
DECLARE @prefix_var nvarchar(50) = 'v_';
DECLARE @prefix_col nvarchar(50) = 'c_';

List my variables, columns, types, nulls

--- List my variables, columns, types, nulls
SET NOCOUNT ON;
SELECT distinct
    '@'+ @prefix_var + REPLACE(c.name,' ','_') AS [-- Variables],
    '['+ @prefix_col + c.name + ']' as [-- Columns],
    t.name + CASE WHEN t.name in ('nvarchar', 'nchar', 'varchar', 'char', 'binary') THEN CASE WHEN c.max_length in (-1,2147483647) THEN '(max)' ELSE ISNULL('(' + CAST(c.max_length AS VARCHAR(30)) + ')', '') END ELSE '' END + CASE WHEN t.name in ('decimal', 'NUMERIC') THEN ISNULL('(' + CAST(c.precision AS VARCHAR(30)) + ',' + CAST(c.scale AS VARCHAR(30)) + ')', '') ELSE '' END AS [-- Types],
    isnull(CASE WHEN c.is_nullable = 0 THEN ' NOT ' ELSE NULL END, ' ') + 'NULL' AS [-- Nulls]
FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))

Select values

--- Select values
SET NOCOUNT ON;
SELECT
    CASE c.column_id WHEN 1 THEN '  ' ELSE ', ' END
    + '@'+ @prefix_var + REPLACE(c.name,' ','_') + ' = ['+ @prefix_col + c.name + '] '
 as [-- Select to value]
FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))
ORDER BY column_id

Print variables

--- Print variables
SET NOCOUNT ON;
SELECT
    'PRINT ''-- @'+ @prefix_var + REPLACE(c.name,' ','_') + ' = '' + CASE WHEN @v_BilletId IS NULL THEN ''NULL'' ELSE '''''''' + CAST(@'+ @prefix_var + REPLACE(c.name,' ','_') + ' AS NVARCHAR(MAX)) + '''''''' END;'
 as [-- print value]
FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))
ORDER BY column_id

Create table

--- Create table
SET NOCOUNT ON;
SELECT distinct
    CASE c.column_id WHEN 1 THEN '  ' ELSE ', ' END
    + '['+ @prefix_col + c.name + '] '
    + t.name + CASE WHEN t.name in ('nvarchar', 'nchar', 'varchar', 'char', 'binary') THEN CASE WHEN c.max_length in (-1,2147483647) THEN '(max)' ELSE ISNULL('(' + CAST(c.max_length AS VARCHAR(30)) + ')', '') END ELSE '' END + CASE WHEN t.name in ('decimal', 'NUMERIC') THEN ISNULL('(' + CAST(c.precision AS VARCHAR(30)) + ',' + CAST(c.scale AS VARCHAR(30)) + ')', '') ELSE '' END  
    + isnull(CASE WHEN c.is_nullable = 0 THEN ' NOT ' ELSE NULL END, ' ') + 'NULL'
    AS [-- Columns to create table]
FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))

Declare variables

-- Declare variables
SET NOCOUNT ON;
SELECT distinct
    'DECLARE '
    + '@'+ @prefix_var + REPLACE(c.name,' ','_')
    + ' AS '
    + t.name + CASE WHEN t.name in ('nvarchar', 'nchar', 'varchar', 'char', 'binary') THEN CASE WHEN c.max_length in (-1,2147483647) THEN '(max)' ELSE ISNULL('(' + CAST(c.max_length AS VARCHAR(30)) + ')', '') END ELSE '' END + CASE WHEN t.name in ('decimal', 'NUMERIC') THEN ISNULL('(' + CAST(c.precision AS VARCHAR(30)) + ',' + CAST(c.scale AS VARCHAR(30)) + ')', '') ELSE '' END  
    AS [-- Declare variables]
FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))

Tranform selected data to XML and XML to Table

-- Tranform selected data to XML and XML to Table
SET NOCOUNT ON;
declare @t table (n int identity, line nvarchar(max));
declare @xml xml
SELECT @xml = ( SELECT * FROM #temp as [RowData] FOR XML AUTO, elements, root('TableData') )
INSERT INTO @t VALUES ('DECLARE @xml AS XML;');
INSERT INTO @t VALUES ('SET @xml = '''+REPLACE ( CAST( @xml as NVARCHAR(max)) , '''', '''''''')+ ''';')
INSERT INTO @t
SELECT
    CASE c.column_id WHEN 1 THEN 'SELECT  ' ELSE ', ' END
    + 'Tbl.Col.value('''
    + c.name
    + '[1],'', '''
    + t.name + CASE WHEN t.name in ('nvarchar', 'nchar', 'varchar', 'char', 'binary') THEN CASE WHEN c.max_length in (-1,2147483647) THEN '(max)' ELSE ISNULL('(' + CAST(c.max_length AS VARCHAR(30)) + ')', '') END ELSE '' END + CASE WHEN t.name in ('decimal', 'NUMERIC') THEN ISNULL('(' + CAST(c.precision AS VARCHAR(30)) + ',' + CAST(c.scale AS VARCHAR(30)) + ')', '') ELSE '' END
    + ''' ) AS ['+ @prefix_col + c.name+']'
FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))
ORDER by c.column_id;
INSERT INTO @t VALUES ('FROM   @xml.nodes(''//TableData//RowData'') Tbl(Col);');
select line as [-- Convert XML to Table] from @t order by n

Sources:
https://stackoverflow.com/questions/11082418/get-column-creation-datatype-for-declaration

https://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server

https://stackoverflow.com/questions/8976414/get-structure-of-temp-table-like-generate-sql-script-and-clear-temp-table-for