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?

129839 次浏览
小开
最佳答案 
select top 0 *
into #mytemptable
from myrealtable
小开

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

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

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;
小开

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

小开

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


提交答案