SQL 使用 SELECT INTO 创建新表并添加主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15508948/
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
Create a new table and adding a primary key using SELECT INTO
提问by user559142
I have a database table that is created using the SELECT INTO SQL syntax. The database is in Access and consists of roughly 500,000 rows. The problem is when I do the join, the unique is the entire row - what I would like is an auto number ID field as the Primary Key.
我有一个使用 SELECT INTO SQL 语法创建的数据库表。该数据库位于 Access 中，包含大约 500,000 行。问题是当我进行连接时，唯一的是整行 - 我想要的是一个自动编号 ID 字段作为主键。
Code I currently have is something like:
我目前拥有的代码类似于：
SELECT INTO new_table
FROM
(SELECT * FROM table a, table b WHERE a.name = b.name)
I was hoping there was a way to add a clause into my SELECT INTO query so I could add a primary key and create the table in one pass - is this possible?
我希望有一种方法可以在我的 SELECT INTO 查询中添加一个子句，这样我就可以添加一个主键并一次性创建表 - 这可能吗？
If not, what is the best way to do this using SQL only?
如果没有，仅使用 SQL 执行此操作的最佳方法是什么？
回答by JodyT
Adding a Primary Keyduring a INSERT INTOstatement is not possible as far as I'm aware. What you can do however, is add an IDENTITYcolumn that auto increments by using SQL Server's IDENTITY()function.
据我所知，Primary Key在INSERT INTO语句中添加 a是不可能的。但是，您可以做的是添加一个IDENTITY使用 SQL Server 的IDENTITY()函数自动递增的列。
Something like this:
像这样的东西：
SELECT
ID = IDENTITY(INT, 1, 1),
col1,
col2
INTO new_table
FROM (SELECT * FROM table a, table b WHERE a.name = b.name)
回答by Gord Thompson
In Access I don't believe that you can accomplish what you want in a single statement. What I think you'll need to do is...
在 Access 中，我不相信您可以在单个语句中完成您想要的操作。我认为你需要做的是...
Create the new table as you described in the question.
Add the AutoNumber column like this:
ALTER TABLE [new_table] ADD COLUMN [ID] AUTOINCREMENT NOT NULL
Make the column you just added the Primary Key:
ALTER TABLE [new_table] ADD CONSTRAINT NewTable_PK PRIMARY KEY (ID)
按照您在问题中的描述创建新表。
添加自动编号列，如下所示：
ALTER TABLE [new_table] 添加列 [ID] AUTOINCREMENT NOT NULL
将刚刚添加的列设为主键：
ALTER TABLE [new_table] 添加约束 NewTable_PK PRIMARY KEY (ID)
回答by Drew Miller
Starting with SQL 2012 there is a DateFromParts function - I think this is a little more readable:
从 SQL 2012 开始，有一个 DateFromParts 函数 - 我认为这更具可读性：
DATEFROMPARTS(YEAR(<datefield>), MONTH(s.DateEnded), 1)
回答by djangojazz
What is the need for creating a primary key and inserting at the same time?
创建主键和同时插入需要什么？
You can create a primary key and table at the same time, but not at insert to my knowledge. You can do this.
您可以同时创建主键和表，但不能在插入时创建。你可以这样做。
create table Tester
(
Id int identity
constraint PK_Id primary key(Id),
description varchar(256)
)
insert into Tester
Select description
from table
or add a drop and create too if you want to ensure the table does not already exist and run this script a bunch. A temp table '#(something)' can follow many of the rules of permanent tables except be used for code level movement and then cleanup when done.
或者，如果您想确保该表尚不存在并大量运行此脚本，则添加一个 drop 并创建。临时表 '#(something)' 可以遵循永久表的许多规则，除了用于代码级移动，然后在完成后进行清理。
if object_id('(schema).(tablename)') is not null
drop table (schema).(tablename)
I created an ever increasing seed with 'identity'. The default is to start at 1 and increment by 1, which serves 95% of simple database primary key wants. It increments automatically so you don't need to worry about it. You can also insert into just specific values if you have more with things like:
我用“身份”创造了一个不断增加的种子。默认是从 1 开始并以 1 递增，这可以满足 95% 的简单数据库主键需求。它会自动递增，因此您无需担心。如果你有更多的东西，你也可以只插入特定的值：
insert into table (col1, col2)
This eliminates the need to insert EVERYTHING and just be the columns you want.
这消除了插入所有内容的需要，只需成为您想要的列。
