SQL 创建具有相同列和类型作为永久表的临时表的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9206433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to create a temp table with same columns and type as a permanent table
提问by Ananth
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.
IE
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?
但是有没有什么方法可以在不提及列名和类型的情况下做到这一点，而是提及另一个具有所需列的表的名称？
回答by nathan gonzalez
select top 0 *
into #mytemptable
from myrealtable
回答by Brooks
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.
注意，临时表将被放入像 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”。
回答by gcbenison
This is a MySQL-specific answer, not sure where else it works --
这是一个特定于 MySQL 的答案，不确定它还能在哪里工作——
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;
回答by Pranay Rana
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
注意：这会创建临时的空副本，但不会创建主键
回答by Rashmi Kant Shrivastwa
select * into #temptable from tablename where 1<>1
回答by Arulmouzhi
Clone Temporary Table Structure to New Physical Table in SQL Server
将临时表结构克隆到 SQL Server 中的新物理表
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.
我们将看到如何在 SQL Server 中将临时表结构克隆到新的物理表。这适用于 Azure SQL db 和本地。
Demo SQL Script
演示 SQL 脚本
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;
METHOD 2
方法二
SELECT TOP 0 * INTO TempTable1 FROM #TempTable;
EXEC SP_HELP TempTable1;
