如何更新身份列在SQL Server?

我有SQL Server数据库，我想改变标识列，因为它开始了有一个大数字10010，它与另一个表相关，现在我有200条记录，我想在记录增加之前修复这个问题。

更改或重置该列的最佳方法是什么?

当前回答

我做了以下事情:

将相关数据移动到临时存储中更新主键/标识列值(删除和创建约束) 用新的外键值重新插入相关数据

我把我的解决方案包装在一个存储过程中:

CREATE PROCEDURE [dbo].[UpdateCustomerLocationId]
    @oldCustomerLocationId INT,
    @newCustomerLocationId INT
AS
  /* 
    Updates CustomerLocation.CustomerLocationId @oldCustomerLocationId to @newCustomerLocationId
    
    Example:
        EXEC [dbo].[UpdateCustomerLocationId] 
            @oldCustomerLocationId = 6154874, 
            @newCustomerLocationId = 50334;
    */

BEGIN

    SET NOCOUNT ON;
    
    -- exit if @oldCustomerLocationId does not exists
    IF NOT EXISTS (SELECT * FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @oldCustomerLocationId)
    BEGIN
        PRINT CONCAT('CustomerLocationId ''', @oldCustomerLocationId, ''' (@oldCustomerLocationId) does not exist in dbo.CustomerLocation');
        RETURN 1; -- 0 = success, > 0 = failure
    END

    -- exit if @newCustomerLocationId already exists
    IF EXISTS (SELECT * FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @newCustomerLocationId)
    BEGIN
        PRINT CONCAT('CustomerLocationId ''', @newCustomerLocationId, ''' (@newCustomerLocationId) already exists in dbo.CustomerLocation');
        RETURN 2; -- 0 = success, > 0 = failure
    END
    

    BEGIN TRAN;


        BEGIN -- MOVE related data into temporary storage

            IF EXISTS (SELECT * FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId) BEGIN
    
                IF OBJECT_ID('tempdb..#CustomerLocationData') IS NOT NULL
                    DROP TABLE #CustomerLocationData;

                SELECT * INTO #CustomerLocationData FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId;
                DELETE t FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId;

            END

        END


        BEGIN -- UPDATE dbo.CustomerLocation

            -- DROP CONSTRAINTs
            ALTER TABLE [dbo].[CustomerLocation] DROP CONSTRAINT [UC_CustomerLocation];

            -- INSERT OLD record with new CustomerLocationId
            SET IDENTITY_INSERT dbo.CustomerLocation ON;

            INSERT INTO dbo.CustomerLocation
            (
                CustomerLocationId, CustomerId, LocationId, CustomerLocationIdent, CustomerLocationIdent2, LocationIdent, LocationName, CustomerDistrictId, 
                CustomerLocationGUID, UpdatedOn, IssueManager, EnrollSelfMonitoring, TemperatureControlDeadlineHour, CreatedOn, OperationBegin, ActiveCustomer, 
                Comments, LocationName2, ParentGroup, TempString1, TempString2, TempString3, TempString4, TempString5, AutoInheritFromLocation, ClassificationPrimary
            )
            SELECT @newCustomerLocationId AS CustomerLocationId, CustomerId,LocationId, CustomerLocationIdent, CustomerLocationIdent2, LocationIdent, LocationName, CustomerDistrictId,
                CustomerLocationGUID, UpdatedOn, IssueManager, EnrollSelfMonitoring, TemperatureControlDeadlineHour, CreatedOn, OperationBegin, ActiveCustomer,
                Comments,LocationName2, ParentGroup, TempString1, TempString2, TempString3, TempString4, TempString5, AutoInheritFromLocation, ClassificationPrimary
            FROM dbo.CustomerLocation 
            WHERE CustomerLocationId = @oldCustomerLocationId;

            SET IDENTITY_INSERT dbo.CustomerLocation OFF;

            -- DELETE OLD record
            DELETE cl FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @oldCustomerLocationId;

            -- ADD CONSTRAINTS
            ALTER TABLE [dbo].[CustomerLocation] ADD CONSTRAINT [UC_CustomerLocation] UNIQUE NONCLUSTERED ([CustomerId], [LocationId]);

        END


        BEGIN -- re-INSERT related data from temporary storage

            IF OBJECT_ID('tempdb..#CustomerLocationData') IS NOT NULL BEGIN
                SET IDENTITY_INSERT dbo.CustomerLocationData ON;
                INSERT INTO dbo.CustomerLocationData (Guid, CustomerLocationId, CustomerLocationDataTypeId, Date, Category, Data)
                SELECT Guid, @newCustomerLocationId CustomerLocationId, CustomerLocationDataTypeId, Date, Category, Data FROM #CustomerLocationData;
                SET IDENTITY_INSERT dbo.CustomerLocationData OFF;
            END

        END


    COMMIT TRAN;

END

2021-10-30 21:54:06

其他回答

ALTER TABLE tablename add newcolumn int
update tablename set newcolumn=existingcolumnname
ALTER TABLE tablename DROP COLUMN existingcolumnname;
EXEC sp_RENAME 'tablename.oldcolumn' , 'newcolumnname', 'COLUMN'
update tablename set newcolumnname=value where condition

然而，上面的代码只有在没有主-外键关系的情况下才能工作

2018-09-27 07:13:11

尝试使用DBCC CHECKIDENT:

DBCC CHECKIDENT ('YourTable', RESEED, 1);

2013-10-03 09:41:38

我已经解决了这个问题，首先使用DBCC，然后使用插入。例如，如果你的桌子是

首先在表中设置新的当前ID Value为NEW_RESEED_VALUE

MyTable { IDCol, 可乐, colB ｝

    DBCC CHECKIDENT('MyTable', RESEED, NEW_RESEED_VALUE)

然后你可以使用

    insert into MyTable (colA, ColB) select colA, colB from MyTable

这将复制所有记录，但使用新的IDCol值作为NEW_RESEED_VALUE开始。然后，在删除/移动外键引用(如果有)后，可以删除ID值较高的重复行。

2014-04-14 11:46:43

这发生在我身上，因为我做了一个合并，它更新了我正在进行合并的ID。

失败的例子(注意ClownID):

    MERGE ArchitectMain.dbo.BackendClowns AS TGT 
   USING (
    SELECT ClownID
        ,ClownName
        ,Description
        ,Active
        ,EmailSubject
        ,AddedBy
        ,Added
    FROM #temptable1
    ) AS SRC(ClownID, ClownName, Description, Active, EmailSubject, AddedBy, Added)
    ON (TGT.ClownID = SRC.ClownID)
WHEN MATCHED
    THEN
        UPDATE
        SET ClownID = SRC.ClownID
            ,ClownName = SRC.ClownName
            ,Description = SRC.Description
            ,Active = SRC.Active
            ,EmailSubject = SRC.EmailSubject
            ,AddedBy = SRC.AddedBy
            ,Added = SRC.Added;

示例(注意ClownID):

MERGE ArchitectMain.dbo.BackendClowns AS TGT
USING (
    SELECT ClownID
        ,ClownName
        ,Description
        ,Active
        ,EmailSubject
        ,AddedBy
        ,Added
    FROM #temptable1
    ) AS SRC(ClownID, ClownName, Description, Active, EmailSubject, AddedBy, Added)
    ON (TGT.ClownID = SRC.ClownID)
WHEN MATCHED
    THEN
        UPDATE
        SET ClownName = SRC.ClownName
            ,Description = SRC.Description
            ,Active = SRC.Active
            ,EmailSubject = SRC.EmailSubject
            ,AddedBy = SRC.AddedBy
            ,Added = SRC.Added;

2022-01-05 19:29:16

我做了以下事情:

将相关数据移动到临时存储中更新主键/标识列值(删除和创建约束) 用新的外键值重新插入相关数据

我把我的解决方案包装在一个存储过程中:

CREATE PROCEDURE [dbo].[UpdateCustomerLocationId]
    @oldCustomerLocationId INT,
    @newCustomerLocationId INT
AS
  /* 
    Updates CustomerLocation.CustomerLocationId @oldCustomerLocationId to @newCustomerLocationId
    
    Example:
        EXEC [dbo].[UpdateCustomerLocationId] 
            @oldCustomerLocationId = 6154874, 
            @newCustomerLocationId = 50334;
    */

BEGIN

    SET NOCOUNT ON;
    
    -- exit if @oldCustomerLocationId does not exists
    IF NOT EXISTS (SELECT * FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @oldCustomerLocationId)
    BEGIN
        PRINT CONCAT('CustomerLocationId ''', @oldCustomerLocationId, ''' (@oldCustomerLocationId) does not exist in dbo.CustomerLocation');
        RETURN 1; -- 0 = success, > 0 = failure
    END

    -- exit if @newCustomerLocationId already exists
    IF EXISTS (SELECT * FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @newCustomerLocationId)
    BEGIN
        PRINT CONCAT('CustomerLocationId ''', @newCustomerLocationId, ''' (@newCustomerLocationId) already exists in dbo.CustomerLocation');
        RETURN 2; -- 0 = success, > 0 = failure
    END
    

    BEGIN TRAN;


        BEGIN -- MOVE related data into temporary storage

            IF EXISTS (SELECT * FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId) BEGIN
    
                IF OBJECT_ID('tempdb..#CustomerLocationData') IS NOT NULL
                    DROP TABLE #CustomerLocationData;

                SELECT * INTO #CustomerLocationData FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId;
                DELETE t FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId;

            END

        END


        BEGIN -- UPDATE dbo.CustomerLocation

            -- DROP CONSTRAINTs
            ALTER TABLE [dbo].[CustomerLocation] DROP CONSTRAINT [UC_CustomerLocation];

            -- INSERT OLD record with new CustomerLocationId
            SET IDENTITY_INSERT dbo.CustomerLocation ON;

            INSERT INTO dbo.CustomerLocation
            (
                CustomerLocationId, CustomerId, LocationId, CustomerLocationIdent, CustomerLocationIdent2, LocationIdent, LocationName, CustomerDistrictId, 
                CustomerLocationGUID, UpdatedOn, IssueManager, EnrollSelfMonitoring, TemperatureControlDeadlineHour, CreatedOn, OperationBegin, ActiveCustomer, 
                Comments, LocationName2, ParentGroup, TempString1, TempString2, TempString3, TempString4, TempString5, AutoInheritFromLocation, ClassificationPrimary
            )
            SELECT @newCustomerLocationId AS CustomerLocationId, CustomerId,LocationId, CustomerLocationIdent, CustomerLocationIdent2, LocationIdent, LocationName, CustomerDistrictId,
                CustomerLocationGUID, UpdatedOn, IssueManager, EnrollSelfMonitoring, TemperatureControlDeadlineHour, CreatedOn, OperationBegin, ActiveCustomer,
                Comments,LocationName2, ParentGroup, TempString1, TempString2, TempString3, TempString4, TempString5, AutoInheritFromLocation, ClassificationPrimary
            FROM dbo.CustomerLocation 
            WHERE CustomerLocationId = @oldCustomerLocationId;

            SET IDENTITY_INSERT dbo.CustomerLocation OFF;

            -- DELETE OLD record
            DELETE cl FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @oldCustomerLocationId;

            -- ADD CONSTRAINTS
            ALTER TABLE [dbo].[CustomerLocation] ADD CONSTRAINT [UC_CustomerLocation] UNIQUE NONCLUSTERED ([CustomerId], [LocationId]);

        END


        BEGIN -- re-INSERT related data from temporary storage

            IF OBJECT_ID('tempdb..#CustomerLocationData') IS NOT NULL BEGIN
                SET IDENTITY_INSERT dbo.CustomerLocationData ON;
                INSERT INTO dbo.CustomerLocationData (Guid, CustomerLocationId, CustomerLocationDataTypeId, Date, Category, Data)
                SELECT Guid, @newCustomerLocationId CustomerLocationId, CustomerLocationDataTypeId, Date, Category, Data FROM #CustomerLocationData;
                SET IDENTITY_INSERT dbo.CustomerLocationData OFF;
            END

        END


    COMMIT TRAN;

END

2021-10-30 21:54:06

如何更新身份列在SQL Server?

推荐文章

最新文章

标签