How to combine SELECT and INSERT commands into a non existent table in SQL Server?

Question

How does one combine the SELECT and INSERT INTO commands and insert the results into a temporary table in SQL Server?

Something like this:

INSERT INTO #TempTable 
    SELECT * FROM MyTable;

Solution 1

You got two options. First one is to create your temp table and then use INSERT INTO ... SELECT, like this:

CREATE TABLE #temp (
  Col1 ...
);

INSERT INTO #temp
SELECT * FROM OtherTable;

The second option is to insert directly in a new temp table:

SELECT  *
INTO    #temp
FROM    OtherTable;

Big difference is that using method 1, you need to specify all the columns in your temp table in advance. Method 2 gives you a temp table that automatically has all the columns of your OtherTable.

Solution 2

You want to insert into a table that doesn't yet exist? Your syntax was close. See here.

SELECT * INTO #BrandNewTempTable FROM MyTable 

Solution 3

Your temporary table needs have the same fields of your origin table

If not you need specify all fields like code below:

INSERT INTO TEMPORARY_TABLE (field1, field1, field1)
SELECT (field1, field1, field1) FROM YOUR_TABLE;

Comments

[GTSouza]

interesting that I did not know, nice one

[2hamed]

Thanks, that's what I was looking for.

[2hamed]

One question. Can this syntax be used in MySql as well?

[Tony]

@EdwinDrood - A quick Google shows the syntax has been supported by MySQL since version 3.23 (dev.mysql.com/doc/refman/4.1/en/…)