SQL INSERT INTO 临时表，并创建一个 IDENTITY 字段，而不先声明临时表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/123557/
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
INSERT INTO a temp table, and have an IDENTITY field created, without first declaring the temp table?
提问by Dale B
I need to select a bunch of data into a temp table to then do some secondary calculations; To help make it work more efficiently, I would like to have an IDENTITY column on that table. I know I could declare the table first with an identity, then insert the rest of the data into it, but is there a way to do it in 1 step?
我需要选择一堆数据到一个临时表中，然后做一些二次计算；为了帮助它更有效地工作，我想在该表上有一个 IDENTITY 列。我知道我可以先用身份声明表，然后将其余数据插入其中，但是有没有办法一步完成？
回答by Matt Rogish
Oh ye of little faith:
哦，你们这些小信：
SELECT *, IDENTITY( int ) AS idcol
INTO #newtable
FROM oldtable
http://msdn.microsoft.com/en-us/library/aa933208(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/aa933208(SQL.80).aspx
回答by boes
You commented: not working if oldtable has an identity column.
您评论说：如果 oldtable 有标识列，则不起作用。
I think that's your answer. The #newtable gets an identity column from the oldtable automatically. Run the next statements:
我想这就是你的答案。#newtable 自动从旧表中获取标识列。运行以下语句：
create table oldtable (id int not null identity(1,1), v varchar(10) )
select * into #newtable from oldtable
use tempdb
GO
sp_help #newtable
It shows you that #newtable does have the identity column.
它向您显示 #newtable 确实具有标识列。
If you don't want the identity column, try this at creation of #newtable:
如果您不想要标识列，请在创建 #newtable 时尝试此操作：
select id + 1 - 1 as nid, v, IDENTITY( int ) as id into #newtable
from oldtable
回答by Catto
Good Question & Matt's was a good answer. To expand on the syntax a little if the oldtable has an identity a user could run the following:
好问题和马特的回答很好。如果 oldtable 具有标识，则要稍微扩展语法，用户可以运行以下命令：
SELECT col1, col2, IDENTITY( int ) AS idcol
INTO #newtable
FROM oldtable
That would be if the oldtable was scripted something as such:
那就是如果 oldtable 被编写了这样的脚本：
CREATE TABLE [dbo].[oldtable]
(
[oldtableID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[col1] [nvarchar](50) NULL,
[col2] [numeric](18, 0) NULL,
)
回答by HLGEM
If you want to include the column that is the current identity, you can still do that but you have to explicitly list the columns and cast the current identity to an int (assuming it is one now), like so:
如果您想包含当前身份的列，您仍然可以这样做，但您必须明确列出列并将当前身份转换为 int（假设它现在是一个），如下所示：
select cast (CurrentID as int) as CurrentID, SomeOtherField, identity(int) as TempID
into #temp
from myserver.dbo.mytable
回答by Amy B
To make things efficient, you need to do declare that one of the columns to be a primary key:
为了提高效率，您需要将其中一列声明为主键：
ALTER TABLE #mytable
ADD PRIMARY KEY(KeyColumn)
That won't take a variable for the column name.
这不会为列名使用变量。
Trust me, you are MUCH better off doing a: CREATE #myTable TABLE(or possibly a DECLARE TABLE @myTable) , which allows you to set IDENTITYand PRIMARY KEYdirectly.
相信我，你好得多做：CREATE #myTable TABLE（或可能DECLARE TABLE @myTable），它允许您设置IDENTITY和PRIMARY KEY直接。
回答by RichM
If after the *, you alias the id column that is breaking the query a secondtime... and give it a new name... it magically starts working.
如果在 * 之后，您为第二次破坏查询的 id 列取别名...并给它一个新名称...它神奇地开始工作。
select IDENTITY( int ) as TempID, *, SectionID as Fix2IDs
into #TempSections
from Files_Sections
回答by Chris Tybur
You could do a Select Into, which would create the table structure on the fly based on the fields you select, but I don't think it will create an identity field for you.
您可以执行 Select Into，它会根据您选择的字段动态创建表结构，但我认为它不会为您创建身份字段。
回答by Hector Sosa Jr
IIRC, the INSERT INTO command uses the schema of the source table to create the temp table. That's part of the reason you can't just try to create a table with an additional column. Identity columns are internally tied to a SQL Server construct called a generator.
IIRC，INSERT INTO 命令使用源表的模式来创建临时表。这就是您不能尝试创建带有附加列的表的部分原因。标识列在内部绑定到称为生成器的 SQL Server 构造。
