SELECT FIELD1,FIELD2,FIELD3 INTO TempTable FROM PHYSICALTABLE;
另一种方法是使用 CREATE TABLE 方法；
CREATE TABLE #TempTable (ID int,NAME varchar(50));
INSERT INTO #TempTable(ID,NAME) VALUES(1,'PERSON');
连接关闭后，或者在临时表上使用 DROP TABLE 命令时，服务器将删除临时表。 除非您使用全局临时表（通过在表名中添加 ##），否则每个连接只能访问自己的临时表。 我读过临时表会导致大表的性能损失，所以我通常只使用临时表来合并两个表，然后对这两个表进行分组+求和。
There are two ways to create temp table. This one will create the table and insert data from PHYSICALTABLE;
SELECT FIELD1,FIELD2,FIELD3 INTO TempTable FROM PHYSICALTABLE;
The other way is to use CREATE TABLE method;
CREATE TABLE #TempTable (ID int,NAME varchar(50));
INSERT INTO #TempTable(ID,NAME) VALUES(1,'PERSON');
Temp tables will be removed by server once the connection is closed, or when you use DROP TABLE command on them. Unless you use global temp tables (By adding ## into table name), every connection only has access to their own temp tables. I had read temp tables causes performance loss on big tables, so I usually only use temp tables to UNION two tables then group by+SUM those two.
-- Create Temp table
CREATE TABLE #temps
(
VId int,
first VARCHAR( 255 ),
surname VARCHAR( 255 ),
DOB DATETIME
PRIMARY KEY (VId)
)
-- Insert some test data
Insert into #temps (Vid, first, surname, DOB)
VALUES (1, 'Bob', 'Jennings','23 Feb 1970')
-- Insert some test data
Insert into #temps (Vid, first, surname, DOB)
VALUES (2, 'John', 'Doe','14 Oct 1965')
-- Select data from the temp table
Select * from #temps
-- Run if you wish to drop the table
-- DROP T ABLE #temps
Here is a quick bit of SQL to create a temp table and select from it
-- Create Temp table
CREATE TABLE #temps
(
VId int,
first VARCHAR( 255 ),
surname VARCHAR( 255 ),
DOB DATETIME
PRIMARY KEY (VId)
)
-- Insert some test data
Insert into #temps (Vid, first, surname, DOB)
VALUES (1, 'Bob', 'Jennings','23 Feb 1970')
-- Insert some test data
Insert into #temps (Vid, first, surname, DOB)
VALUES (2, 'John', 'Doe','14 Oct 1965')
-- Select data from the temp table
Select * from #temps
-- Run if you wish to drop the table
-- DROP T ABLE #temps
The following guide appears pretty good place to start for detailed information about temporary tables (& table variables) including life-time, sharing etc.:
Comparing them to 'normal' tables i would say the biggest differences is essentially that normal tables persist in the database and thus should be used whenever you need to store the data you are working with, wheras temporary tables should be used if just working within the context of a query/stored procedure etc...
发布评论
评论（3）
创建临时表有两种方法。
这将创建表并从 PHYSICALTABLE 插入数据；
另一种方法是使用 CREATE TABLE 方法；
连接关闭后，或者在临时表上使用 DROP TABLE 命令时，服务器将删除临时表。 除非您使用全局临时表（通过在表名中添加 ##），否则每个连接只能访问自己的临时表。 我读过临时表会导致大表的性能损失，所以我通常只使用临时表来合并两个表，然后对这两个表进行分组+求和。
There are two ways to create temp table.
This one will create the table and insert data from PHYSICALTABLE;
The other way is to use CREATE TABLE method;
Temp tables will be removed by server once the connection is closed, or when you use DROP TABLE command on them. Unless you use global temp tables (By adding ## into table name), every connection only has access to their own temp tables. I had read temp tables causes performance loss on big tables, so I usually only use temp tables to UNION two tables then group by+SUM those two.
这是创建临时表并从中进行选择的快速 SQL 语句
Here is a quick bit of SQL to create a temp table and select from it
以下指南似乎是一个很好的起点，可以了解有关临时表（和表变量）的详细信息，包括生命周期、共享等：
SQL Server 临时表真的有必要吗？ 还考虑出于性能原因使用临时表。
将它们与“普通”表进行比较，我想说最大的区别本质上是普通表保留在数据库中，因此每当您需要存储正在使用的数据时就应该使用普通表，而如果只是在数据库中工作则应该使用临时表。查询/存储过程等的上下文...
The following guide appears pretty good place to start for detailed information about temporary tables (& table variables) including life-time, sharing etc.:
Are SQL Server Temp Tables Really Necessary? also looks at using temporary tables for performance reasons.
Comparing them to 'normal' tables i would say the biggest differences is essentially that normal tables persist in the database and thus should be used whenever you need to store the data you are working with, wheras temporary tables should be used if just working within the context of a query/stored procedure etc...