创建具有与永久性表相同的列和类型的临时表的最佳方法

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)

我需要创建一个与永久性表具有相同列和类型的临时表。最好的方法是什么?(永久表有100列以上)

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?

但是，有没有什么方法可以在不提到列名和类型的情况下实现它，而只提到另一个具有所需列的表的名称呢?

5 个解决方案

#1

select top 0 *
into #mytemptable
from myrealtable

#2

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

我知道这个问题由来已久，但是对于任何想要寻找PostgreSQL解决方案的人来说，它是:

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.

注意，temp表将被放入类似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".

如果原始表开始时没有任何数据，可以省略“LIMIT 0”。

#3

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

注意:这会创建一个临时的temp副本，但它不会创建主键。

#4

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

这是一个特定于sql的答案，不确定它还能在哪里工作—

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:

以下是postgres的作品;不幸的是，不同的RDBMS似乎并不一致:

CREATE TEMPORARY TABLE temp_foo AS SELECT * FROM foo;

#5

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

#1

select top 0 *
into #mytemptable
from myrealtable

#2