--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

您不能更新标识列。 SQL Server不允许更新标识列，这与使用更新语句更新其他列不同。

尽管有一些替代方案可以实现类似的需求。

当需要为新记录更新标识列值时

使用DBCC CHECKIDENT检查表的当前标识值，如果需要，则更改标识值。

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

当需要更新现有记录的标识列值时

使用IDENTITY_INSERT，它允许显式的值插入到表的标识列中。

SET IDENTITY_INSERT YourTable {ON|OFF}

例子:

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF

不允许更新: 但是你可以

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

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

这发生在我身上，因为我做了一个合并，它更新了我正在进行合并的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;
            

您可以使用以下代码创建一个新表。

SELECT IDENTITY (int, 1, 1) AS id, column1, column2
INTO dbo.NewTable
FROM dbo.OldTable

然后删除旧的db，并将新db重命名为旧db的名称。注意:columnn1和column2表示旧表中希望保留在新表中的所有列。

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

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