CODEWITHSUNDEEP
sqlsql-server
Ananth
Ananth

Reputation: 10730

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

I need to create a temp table with same columns and type as a permanent table. What is the best way to do it? (The Permanent table has over 100 columns)

i.e.

Usually I create table like this. 

DECLARE  #TT TABLE(              
  member_id INT,    
  reason varchar(1),    
  record_status varchar(1) ,    
  record_type varchar(1)    
 )

But is there any way to do it without mentioning the column names and type, but mention the name of another table with the required columns?

Upvotes: 89

Views: 158604

Answers (6)

Arulmouzhi
Arulmouzhi

Reputation: 2254

Clone Temporary Table Structure to New Physical Table in SQL Server

enter image description here

we will see how to Clone Temporary Table Structure to New Physical Table in SQL Server.This is applicable for both Azure SQL db and on-premises.

Demo SQL Script

IF OBJECT_ID('TempDB..#TempTable') IS NOT NULL
    DROP TABLE #TempTable;

SELECT 1 AS ID,'Arul' AS Names
INTO
#TempTable;

SELECT * FROM #TempTable;

METHOD 1

SELECT * INTO TempTable1 FROM #TempTable WHERE 1=0;

EXEC SP_HELP TempTable1;

enter image description here

METHOD 2

SELECT TOP 0 * INTO TempTable1 FROM #TempTable;

EXEC SP_HELP TempTable1;

enter image description here

Upvotes: 1

Brooks
Brooks

Reputation: 7410

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".

Upvotes: 18

gcbenison
gcbenison

Reputation: 11963

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;

Upvotes: 9

Pranay Rana
Pranay Rana

Reputation: 176956

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

Upvotes: 9

Rashmi Kant Shrivastwa
Rashmi Kant Shrivastwa

Reputation: 1167

select * into #temptable from tablename where 1<>1

Upvotes: 1

nathan gonzalez
nathan gonzalez

Reputation: 12017

select top 0 *
into #mytemptable
from myrealtable

Upvotes: 171

Related Questions