DBCC CHECKIDENT(table_name, RESEED, value)

Table_name =给出要重置的表的值

Value =初始值为0，以1开始单位列

就我所见，有几种方法可以做到这一点。，但在我看来最好和最快的方法是以下一种:

标识列有一个计数器，它不一定与已注册的列相同，您可以使用以下SQL命令查看该计数器的值:

DBCC CHECKIDENT('tableName', NORESEED);

然后，如果你想编辑标识列，你将不能，但我建议在重新播种计数器到你需要的数字后，做一个新的寄存器。要重新播种计数器，使用以下命令:

DBCC CHECKIDENT('tableName', RESEED, desiredNumber);

--before running this make sure Foreign key constraints have been removed that reference the ID. 

--set table to allow identity to be inserted
SET IDENTITY_INSERT yourTable ON;
GO
--insert everything into a temp table
SELECT * 
INTO #tmpYourTable
FROM yourTable

--clear your table
DELETE FROM yourTable
--insert back all the values with the updated ID column
INSERT INTO yourTable (IDCol, OtherCols)
SELECT ID+1 as updatedID --put any other update logic to the ID here
, OtherCols FROM #tmpYourTable
--drop the temp table
DROP TABLE #tmpYourTable
--put identity back to normal
SET IDENTITY_INSERT yourTable OFF;
GO

你需要

set identity_insert YourTable ON

然后删除行并重新插入不同的标识。

插入完成后，不要忘记关闭identity_insert

set identity_insert YourTable OFF

我做了以下事情:

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

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

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

不允许更新: 但是你可以

用正确的键插入新数据 删除注册

Import:所有字段必须在insert into中声明 示例:reg 5必须更改为4:

    set IDENTITY_INSERT Gastos_ReclamacionCausa on
insert into Gastos_ReclamacionCausa
    (IDCausa,TextoCombo,Asunto,Mensaje,EsBaja)
select 4,TextoCombo,Asunto,Mensaje,EsBaja from Gastos_ReclamacionCausa where idcausa=5
delete from  Gastos_ReclamacionCausa where idcausa = 5
set IDENTITY_INSERT Gastos_ReclamacionCausa off