SQL Server，如何在创建表后设置自动增量而不丢失数据？

Question

SQL Server，如何在创建表后设置自动增量而不丢失数据？

sql-serversql-server-2008identityauto-increment

51

我在SQL Server 2008中有一个名为table1的表，并且其中有记录。

我希望将主键列table1_Sno设置为自动递增列。是否可以在不传输或克隆表中的任何数据的情况下完成此操作？

我知道可以使用ALTER TABLE添加自动递增列，但是我是否可以简单地将AUTO_INCREMENT选项添加到现有的主键列中？

- arun

不确定您是否可以执行此操作：请参阅https://dev59.com/YW445IYBdhLWcg3wcJ2O - Femi

7个回答

5

SQL Server：如何在已有行的表上设置自动递增：

这种策略会将行物理复制两次，如果要复制的表非常大，这可能需要更长的时间。

你可以先保存数据，删除并重新构建带有自动递增和主键的表，然后再将数据加载回来。

下面举个例子演示：

第一步，创建没有主键或自动递增的表foobar：

CREATE TABLE foobar(
    id int NOT NULL,
    name nchar(100) NOT NULL,
)

步骤二，插入一些行。

insert into foobar values(1, 'one');
insert into foobar values(2, 'two');
insert into foobar values(3, 'three');

第三步，将 foobar 数据复制到临时表中：

select * into temp_foobar from foobar

第四步，删除表foobar：

drop table foobar;

第五步，使用主键和自动增量属性重新创建您的表：

CREATE TABLE foobar(
    id int primary key IDENTITY(1, 1) NOT NULL,
    name nchar(100) NOT NULL,
)

第六步，将临时表中的数据插入回 foobar。

SET IDENTITY_INSERT temp_foobar ON
INSERT into foobar (id, name) select id, name from temp_foobar;

步骤7，删除临时表并检查是否已成功：

drop table temp_foobar;
select * from foobar;

你应该得到这个，当你检查 foobar 表时，id 列是自增的，id 是主键：

1    one
2    two
3    three

- Eric Leschinski

1

当我尝试这样做时，我收到了错误消息：“表'temp_foobar'没有标识属性。无法执行SET操作。”我是否漏掉了什么步骤？ - dallin

为了使其正常工作，我将其更改为“SET IDENTITY_INSERT foobar ON;”作为自己的语句。此外，我还必须将“NOT FOR REPLICATION”选项设置为是，否则在INSERT语句上会出现“必须为标识列指定显式值”的错误。 - dallin

当我执行 "SET IDENTITY_INSERT temp_foobar ON" 时，仍然会收到 "表 'temp_foobar' 没有标识属性。无法执行 SET 操作。" 的错误提示。 - Be Chiller Too

3

如果您想通过设计器完成此操作，可以按照此处的说明执行。

此链接提供了更详细的说明。

- Daveo

2

是的，您可以这样做。转到 工具 > 设计器 > 表格和设计器 ，然后取消选中 "防止保存更改以防止表格重新创建"。

- Peter Pauletto

问题指定“无数据传输”。SSMS会创建一个新表并将所有数据复制到其中。 - Martin Smith

1

不可以在已有数据的列上添加自动递增选项，我认为你提到的选项是最好的。

请看这里。

- Homam

1

如果您不想添加新列，并且可以保证当前的 int 列是唯一的，那么您可以将所有数据选择到一个临时表中，删除该表并重新创建指定了 IDENTITY 列。然后使用 SET IDENTITY INSERT ON 将临时表中的所有数据插入到新表中。

- Duncan Howe

0

以下脚本可以是一个好的解决方案。在大数据中也可适用。

ALTER DATABASE WMlive SET RECOVERY SIMPLE WITH NO_WAIT

ALTER TABLE WMBOMTABLE DROP CONSTRAINT PK_WMBomTable

ALTER TABLE WMBOMTABLE drop column BOMID

ALTER TABLE WMBOMTABLE ADD BomID int IDENTITY(1, 1) NOT NULL;

ALTER TABLE WMBOMTABLE ADD CONSTRAINT PK_WMBomTable PRIMARY KEY CLUSTERED (BomID);

ALTER DATABASE WMlive SET RECOVERY FULL WITH NO_WAIT

- Jatin Dave

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Martin Smith · Accepted Answer

更改IDENTITY属性只是元数据的更改。但要直接更新元数据，需要在单用户模式下启动实例，并在sys.syscolpars中操纵某些列，这是未记录/不支持的操作，也不建议这样做或提供任何其他详细信息。

对于在SQL Server 2012+上遇到此问题的人们，最简单的方法是创建一个SEQUENCE对象，并将next value for seq设置为列默认值。

或者，对于之前的版本（从2005年开始），this connect item上发布的解决方法是一种完全受支持的方式，可以在不使用ALTER TABLE...SWITCH进行数据操作的情况下完成此操作。此外，在MSDN here上也有相关博客。尽管实现此操作的代码并不是非常简单，还存在一些限制-例如被更改的表不能是外键约束的目标。

示例代码：设置没有identity列的测试表。

CREATE TABLE dbo.tblFoo 
(
bar INT PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)


INSERT INTO dbo.tblFoo (bar)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values v1, master..spt_values v2

将其修改为具有`identity`列（几乎即时）。

BEGIN TRY;
    BEGIN TRANSACTION;

    /*Using DBCC CHECKIDENT('dbo.tblFoo') is slow so use dynamic SQL to
      set the correct seed in the table definition instead*/
    DECLARE @TableScript nvarchar(max)
    SELECT @TableScript = 
    '
    CREATE TABLE dbo.Destination(
        bar INT IDENTITY(' + 
                     CAST(ISNULL(MAX(bar),0)+1 AS VARCHAR) + ',1)  PRIMARY KEY,
        filler CHAR(8000),
        filler2 CHAR(49)
        )

        ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination;
    '       
    FROM dbo.tblFoo
    WITH (TABLOCKX,HOLDLOCK)

    EXEC(@TableScript)


    DROP TABLE dbo.tblFoo;

    EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT';


    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;

测试结果。

INSERT INTO dbo.tblFoo (filler,filler2) 
OUTPUT inserted.*
VALUES ('foo','bar')

给予

bar         filler    filler2
----------- --------- ---------
10001       foo       bar

清理

DROP TABLE dbo.tblFoo

SQL Server，如何在创建表后设置自动增量而不丢失数据？

将其修改为具有identity列（几乎即时）。

测试结果。

给予

清理

SQL Server：如何在已有行的表上设置自动递增：

将其修改为具有`identity`列（几乎即时）。