Entry

How do you create a new table to contain the results of a select query?

Apr 10th, 2007 14:12
Jason Benson, Paul Parmar,

If you would like to just hold the results temporarily you can use a 
variable table or a temporary table:

-----------------------------------------------------------------
Variable Table Example:
-----------------------------------------------------------------

DECLARE @MyTable TABLE (Column1 VarChar(255))

INSERT INTO @MyTable (Column1)
   SELECT FirstName From Customers

(You can reference @MyTable for the duration of your query as a normal 
table.)

-----------------------------------------------------------------
Temporary Table Example:
-----------------------------------------------------------------

SELECT FirstName INTO #tmpMyTable
     From Customers
     
OR
CREATE TABLE #tmpMyTable (Column1 VarChar(255))

INSERT INTO #tmpMyTable (Column1)
   SELECT FirstName From Customers


The INTO statement will create the table you are selecting into (and 
will produce an error if the table already exists)

When using Temporary Tables, remember to DROP TABLE at the end of your 
query.

-----------------------------------------------------------------


-----------------------------------------------------------------
User Tables:
-----------------------------------------------------------------

To select data from one query into a user table you can use the same 
syntax as a Temporary Table (Create Table or Select * INTO).

If you already have a table you can use a standard select:

   If Columns Match perfectly (ill advised method):
   Insert INTO MyTable
      Select * From Customers

   It's recommended to always define columns:
   Insert INTO MyTable (Column1, Column2, Column3, Etc)
     Select Column1, Column2, Column3, Etc


Hope that helps,
JB