Create permanent table based on temporary table in SQL Server

Question

I have a temp table which aggregates columns from multiple tables.

I would like to convert this temp table into a permanent table without explicit specifying the column names and their types.

Don't know if i have explained this well enough

Answer 1

You can use SELECT ... INTO:

SELECT *
INTO dbo.normal_table
FROM #temp_table
-- WHERE 1 = 2;   --add if you only want table structure and not actual data

Things to check after table creation:

• IDENTITY column and current value (may need reseeding)
• DEFAULT values of column(if temp table has defaults, normal table needs to be ALTERed)
• COLLATION tempdb may have different collation than your database
• size of columns, precision and scale (VARCHAR,NVARCHAR,CHAR, DECIMAL..) if SELECT contains expressions

If temp table does not contain IDENTITY column you can add one using:

SELECT ID  = IDENTITY(INT,1,1)
     ,col1 = NULL   -- NULL by default are treated as INT so you may need casting
     ,col2 = CAST(NULL AS DECIMAL(38,10))
     ,t.*
INTO dbo.table_name
FROM #temp t