Best way to create a temp table with same columns and type as a permanent table

Sortest one...

select top 0 * into #temptable from mytable

Note : This creates an empty copy of temp, But it doesn't create a primary key

I realize this question is extremely old, but for anyone looking for a solution specific to PostgreSQL, it's:

CREATE TEMP TABLE tmp_table AS SELECT * FROM original_table LIMIT 0;

Note, the temp table will be put into a schema like pg_temp_3.

This will create a temporary table that will have all of the columns (without indexes) and without the data, however depending on your needs, you may want to then delete the primary key:

ALTER TABLE pg_temp_3.tmp_table DROP COLUMN primary_key;

If the original table doesn't have any data in it to begin with, you can leave off the "LIMIT 0".

select top 0 *
into #mytemptable
from myrealtable

This is a MySQL-specific answer, not sure where else it works --

You can create an empty table having the same column definitions with:

CREATE TEMPORARY TABLE temp_foo LIKE foo;

And you can create a populated copy of an existing table with:

CREATE TEMPORARY TABLE temp_foo SELECT * FROM foo;

And the following works in postgres; unfortunately the different RDBMS's don't seem very consistent here:

CREATE TEMPORARY TABLE temp_foo AS SELECT * FROM foo;