Create Microsoft SQL Temp Tables (without declaring columns – like Informix)?

Question

I recently changed positions, and came from an Informix database environment, where I could use SQL statements to select one or more columns ... and direct the output to a temporary table. In Informix, for temp tables, I neither had to declare the column names, nor the column lengths (only the name of a temp table) - I could simply write:

select [columnname1, columnname2, columnname3 ..] from [database.tablename] where... etc. into temp tablename1 with no log;

Note that in Informix, the temp table stores the column names by default... as well as the data types [by virtue of the data-type being stored in the temp table]. So, if the above statement was executed, then a developer could merely write:

select columname1, columnname2, etc. from tablename1

In my experience, I found this method was very useful - for numerous reasons ('slicing/dicing' the data, using various data sources, etc.)... as well as tremendously fast and efficient.

However, now I am using Microsoft SQL Server, I have not found a way (yet) do the same. In SQL Server, I must declare each column, along with its length:

Create table #tablename1 ( column1 numeric(13,0) );

insert into #tablename1(column1) select [column] from [database.tablename] where …

[Then use the info, as needed]:

select * from #tablename1 [ and do something...]

Drop table #tablename1

Does anyone know of how I could do this and/or set-up this capability in Microsoft SQL Server? I looked at anonymous tables (i.e. Table-Value constructors: http://technet.microsoft.com/en-us/library/dd776382.aspx)... but the guidance stated that declaring the columns was still necessary.

Thanks ahead of time - jrd

Answer 1

The syntax is :

select [columnname1], [columnname2], [columnname3]  into tablename1 from [database].[schema].[tablename] where... 

prefix tablename1 with # if you want the table to be temporary

Answer 2

It should be noted that, while you can use the syntax below:

SELECT col1, col2...
INTO #tempTable1
FROM TABLEA

You should also give your calculated columns names as well. Such that you get:

SELECT col1, col2...,AVG(col9) AS avgCol9
INTO #tempTable1
FROM TABLEA