CODEWITHSUNDEEP
sqlsql-server
jrdunson
jrdunson

Reputation: 339

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

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

Upvotes: 3

Views: 7370

Answers (3)

wergeld
wergeld

Reputation: 14442

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

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

Its very simple in sql server as well all you have to do is 

SELECT Column1, Column2, Column3,......  INTO #Temp
FROM Table_Name

This statement will Create a Temp Table on fly copying Data and DataType all over to the Temp Table. # sign makes this table a temporary table , you can also Create a Table by using the same syntax but with out the # sign, something like this 

SELECT Column1, Column2, Column3,......  INTO New_Table_Name
FROM Table_Name

Upvotes: 0

jazzytomato
jazzytomato

Reputation: 7214

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

Upvotes: 2

Related Questions