SQL Server 为现有表添加自增主键
如标题所示,我有一个现有表,其中已填充 150000 条记录。我添加了一个 Id 列(当前为空)。
我假设我可以运行一个查询来用增量数字填充此列,然后设置为主键并打开自动增量。这是正确的方法吗?如果是这样,我该如何填写初始数字?
As the title, I have an existing table which is already populated with 150000 records. I have added an Id column (which is currently null).
I'm assuming I can run a query to fill this column with incremental numbers, and then set as primary key and turn on auto increment. Is this the correct way to proceed? And if so, how do I fill the initial numbers?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(17)
此答案是对最高投票答案的一个小补充,适用于 SQL Server。问题要求自动增量主键,当前答案确实添加了主键,但没有标记为自动增量。下面的脚本检查列是否存在,并在启用自动增量标志的情况下添加它。
This answer is a small addition to the highest voted answer and works for SQL Server. The question requested an auto increment primary key, the current answer does add the primary key, but it is not flagged as auto-increment. The script below checks for the columns, existence, and adds it with the autoincrement flag enabled.
由设计师您可以设置身份 (1,1)
右键单击表 =>设计=>部分左侧(右键单击)=>属性=>在身份列中选择 #column
属性
身份栏
by the designer you could set identity (1,1)
right click on tbl => desing => in part left (right click) => properties => in identity columns select #column
Properties
idendtity column
如果您的表使用其主键或 foriegen 键与其他表有关系,则可能无法更改您的表。因此您需要删除并再次创建表。
要解决这些问题,您需要通过右键单击数据库来生成脚本,并在高级选项中设置数据类型以编写方案和数据的脚本。之后,使用此脚本更改您的列,以通过运行其查询来识别和重新生成表。
您的查询将如下所示:
If your table has relationship with other tables using its primary or foriegen key, may be it is impossible to alter your table. so you need to drop and create the table again.
To solve these problems you need to Generate Scripts by right click on the database and in advanced option set type of data to script to scheme and data. after that, using this script with the changing your column to identify and regenerate the table using run its query.
your query will be like here:
我看到一个解决方案不久前已被接受,但我想我应该添加另一种方法,因为我处于类似的位置。
接受的解决方案更简单,但不提供控制排序的能力(就像我的情况一样保留日期时间列的时间顺序)。
在 SQL Server 中,我的解决方案是:
代码:
<强>完全披露:我通过一些积极的研究(这篇文章有所帮助)和对 ChatGPT 的一些询问得出了这个解决方案。
I see a solution to this was accepted a while ago, but I thought I'd add another approach since I'm in a similar position.
The accepted solution is simpler, but does not offer the ability to control the ordering (like preserving chronological ordering of a datetime column as is my case).
In SQL Server, my solution was to:
Code:
Full disclosure: I arrived at this solution through some active researching (this post helped), and some interrogation of ChatGPT.
这是一个你可以尝试的想法。
原始表 - 无标识列 table1
创建一个新表 - 调用 table2 以及标识列。
将数据从 table1 复制到 table2 - 标识列会自动填充自动递增的数字。
将原始表 - table1 重命名为 table3
将新表 - table2 重命名为 table1(原始表)
现在您已经拥有包含标识列并为现有数据填充的 table1。
确保没有问题并正常工作后,在不再需要时删除 table3。
Here is an idea you can try.
Original table - no identity column table1
create a new table - call table2 along with identity column.
copy the data from table1 to table2 - the identity column is populated automatically with auto incremented numbers.
rename the original table - table1 to table3
rename the new table - table2 to table1 (original table)
Now you have the table1 with identity column included and populated for the existing data.
after making sure there is no issue and working properly, drop the table3 when no longer needed.
创建一个具有不同名称和相同列、主键和外键关联的新表,并将其链接到代码的 Insert 语句中。
例如:对于 EMPLOYEE,请替换为 EMPLOYEES。
但是,您必须删除现有的 EMPLOYEE 表或根据您的要求进行一些调整。
Create a new Table With Different name and same columns, Primary Key and Foreign Key association and link this in your Insert statement of code.
For E.g : For EMPLOYEE, replace with EMPLOYEES.
However, you have to either delete the existing EMPLOYEE Table or do some adjustment according to your requirement.
alter table /** 粘贴 tabal 的名称 **/
add id int IDENTITY(1,1)
delete from /** 粘贴选项卡的名称 **/
where id in
(
select a.id FROM /** 将选项卡的名称 / 粘贴为
左外连接 (
选择 MIN(id) 作为 id
FROM / 粘贴选项卡的名称/
分组依据
/ 粘贴列 c1,c2 .... **/
WHERE t1.id IS NULL
)
alter table /** 粘贴 tabal 的名称 **/
删除列 ID
alter table /** paste the tabal's name **/
add id int IDENTITY(1,1)
delete from /** paste the tabal's name **/
where id in
(
select a.id FROM /** paste the tabal's name / as a
LEFT OUTER JOIN (
SELECT MIN(id) as id
FROM / paste the tabal's name /
GROUP BY
/ paste the columns c1,c2 .... **/
WHERE t1.id IS NULL
)
alter table /** paste the tabal's name **/
DROP COLUMN id
试试下面的代码:
Try This Code Bellow:
这在 MariaDB 中有效,所以我只能希望它在 SQL Server 中也有效:删除刚刚插入的 ID 列,然后使用以下语法:-
不需要另一个表。这只是插入一个 id 列,使其成为主索引,并用顺序值填充它。如果 SQL Server 不这样做,我很抱歉浪费了您的时间。
This works in MariaDB, so I can only hope it does in SQL Server: drop the ID column you've just inserted, then use the following syntax:-
No need for another table. This simply inserts an id column, makes it the primary index, and populates it with sequential values. If SQL Server won't do this, my apologies for wasting your time.
尝试这样的事情(首先在测试台上):
我根本没有测试过这个,所以要小心!
Try something like this (on a test table first):
I have not tested this at all, so be careful!
这可能有用
This could be useful
不 - 您必须以相反的方式执行此操作:从一开始就将其添加为
INT IDENTITY
- 当您执行此操作时,它将填充身份值:然后您可以将其设为主要关键:
或者如果您喜欢一步完成所有操作:
No - you have to do it the other way around: add it right from the get go as
INT IDENTITY
- it will be filled with identity values when you do this:and then you can make it the primary key:
or if you prefer to do all in one step:
您无法“打开”IDENTITY:这是表重建。
如果您不关心数字顺序,则可以一次性添加带有 IDENTITY 的 NOT NULL 列。 150k 行并不是很多。
如果您需要保留某些数字顺序,请相应地添加数字。然后使用 SSMS 表设计器设置 IDENTITY 属性。这允许您生成一个脚本,该脚本将为您执行列删除/添加/保留数字/重新设定种子。
You can't "turn on" the IDENTITY: it's a table rebuild.
If you don't care about the number order, you'd add the column, NOT NULL, with IDENTITY in one go. 150k rows isn't a lot.
If you need to preserve some number order, then add the numbers accordingly. Then use the SSMS table designer to set the IDENTITY property. This allows you to generate a script which will do the column drop/add/keep numbers/reseed for you.
我遇到了这个问题,但无法使用身份列(由于各种原因)。
我决定这样做:
借用 此处。
I had this issue, but couldn't use an identity column (for various reasons).
I settled on this:
Borrowed from here.
如果该列已存在于表中且为空,则可以使用以下命令更新该列(替换 id、tablename 和 tablekey ):
If the column already exists in your table and it is null, you can update the column with this command (replace id, tablename, and tablekey ):
当我们在现有表中添加标识列时,它将自动填充,无需手动填充。
When we add and identity column in an existing table it will automatically populate no need to populate it manually.