SQL Server 从另一个表创建临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2190095/
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
SQL Server creating a temporary table from another table
提问by DeveloperChris
I am looking to create a temporary table which is used as an intermediate table while compiling a report.
我希望创建一个临时表，在编译报告时用作中间表。
For a bit of background I am porting a VB 6 app to .net
对于一些背景知识，我正在将 VB 6 应用程序移植到 .net
To create the table I can use...
要创建表，我可以使用...
SELECT TOP 0 * INTO #temp_copy FROM temp;
This creates an empty copy of temp, But it doesn't create a primary key
这会创建临时的空副本，但不会创建主键
Is there a way to create a temp table plus the constraints?
有没有办法创建一个临时表加上约束？
Should I create the constraints afterwards?
之后我应该创建约束吗？
Or am I better off just creating the table using create table, I didn't want to do this because there are 45 columns in the table and it would fill the procedure with a lot of unnecessary cruft.
或者我最好只使用 create table 创建表，我不想这样做，因为表中有 45 列，它会用很多不必要的 cruft 填充程序。
The table is required because a lot of people may be generating reports at the same time so I can't use a single intermediary table
该表是必需的，因为很多人可能同时生成报告，所以我不能使用单个中间表
采纳答案by Mitch Wheat
Do you actually need a Primary Key? If you are flitering and selecting only the data needed by the report won't you have to visit every row in the temp table anyway?
你真的需要一个主键吗？如果您过滤并仅选择报告所需的数据，您是否不必访问临时表中的每一行？
回答by George Dontas
By design, SELECT INTO does not carry over constraints (PK, FK, Unique), Defaults, Checks, etc. This is because a SELECT INTO can actually pull from numerous tables at once (via joins in the FROM clause). Since SELECT INTO creates a new table from the table(s) you specify, SQL really has no way of determining which constraints you want to keep, and which ones you don't want to keep.
按照设计，SELECT INTO 不会继承约束（PK、FK、Unique）、默认值、检查等。这是因为 SELECT INTO 实际上可以一次从多个表中提取（通过 FROM 子句中的连接）。由于 SELECT INTO 根据您指定的表创建一个新表，SQL 确实无法确定您想要保留哪些约束，以及不想保留哪些约束。
You could write a procedure/script to create the constraint automatically, but it's probably too much effort for minimal gain.
您可以编写一个过程/脚本来自动创建约束，但是为了最小的收益可能需要付出太多的努力。
回答by gbn
You'd have to do one or the other:
你必须做一个或另一个：
- add the PK/indexes afterwards
- explicitly declare the temp table with constraints.
- 之后添加 PK/索引
- 显式声明带有约束的临时表。
I'd also do this rather then TOP 0
我也会这样做而不是TOP 0
SELECT * INTO #temp_copy FROM temp WHERE 1 = 0;
