如何更新身份列在SQL Server?

尝试使用DBCC CHECKIDENT:

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

您不能更新标识列。

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

你也可以使用SET IDENTITY INSERT来允许你在标识列中插入值。

例子:

SET IDENTITY_INSERT dbo.Tool ON
GO

然后你可以在单位列中插入你需要的值。

如果你的问题答对了，你想做的是

update table
set identity_column_name = some value

让我告诉你，这不是一个简单的过程，使用它是不可取的，因为它可能有一些foreign key相关。

但这里有步骤来做，请采取back-up的表

步骤1-选择表的设计视图

步骤2-关闭标识列

现在你可以使用update查询了。

现在redo步骤1和步骤2和打开标识列

参考< a href = " http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/ " > < / >

你需要

set identity_insert YourTable ON

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

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

set identity_insert YourTable OFF

DBCC CHECKIDENT(table_name, RESEED, value)

Table_name =给出要重置的表的值

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

c#程序员使用命令构建器的完整解决方案

首先，你要知道这些事实:

• 在任何情况下，都不能修改标识列，因此必须删除该行并重新添加新的标识。
• 不能从列中删除标识属性(必须删除到列)
• .net中的自定义命令构建器总是跳过标识列，因此不能将其用于此目的。

一旦知道了这个，你要做的就是。要么编写自己的SQL Insert语句，要么编写自己的Insert命令构建器。或者用我为你设计的这个。给定一个数据表，生成SQL插入脚本:

public static string BuildInsertSQLText ( DataTable table )
{
StringBuilder sql = new StringBuilder(1000,5000000);
StringBuilder values = new StringBuilder ( "VALUES (" );
bool bFirst = true;
bool bIdentity = false;
string identityType = null;


foreach(DataRow myRow in table.Rows)
{
sql.Append( "\r\nINSERT INTO " + table.TableName + " (" );


foreach ( DataColumn column in table.Columns )
{
if ( column.AutoIncrement )
{
bIdentity = true;


switch ( column.DataType.Name )
{
case "Int16":
identityType = "smallint";
break;
case "SByte":
identityType = "tinyint";
break;
case "Int64":
identityType = "bigint";
break;
case "Decimal":
identityType = "decimal";
break;
default:
identityType = "int";
break;
}
}
else
{
if ( bFirst )
bFirst = false;
else
{
sql.Append ( ", " );
values.Append ( ", " );
}
sql.Append ("[");
sql.Append ( column.ColumnName );
sql.Append ("]");


//values.Append (myRow[column.ColumnName].ToString() );


if (myRow[column.ColumnName].ToString() == "True")
values.Append("1");
else if (myRow[column.ColumnName].ToString() == "False")
values.Append("0");
else if(myRow[column.ColumnName] == System.DBNull.Value)
values.Append ("NULL");
else if(column.DataType.ToString().Equals("System.String"))
{
values.Append("'"+myRow[column.ColumnName].ToString()+"'");
}
else
values.Append (myRow[column.ColumnName].ToString());
//values.Append (column.DataType.ToString() );
}
}
sql.Append ( ") " );
sql.Append ( values.ToString () );
sql.Append ( ")" );


if ( bIdentity )
{
sql.Append ( "; SELECT CAST(scope_identity() AS " );
sql.Append ( identityType );
sql.Append ( ")" );
}
bFirst = true;
sql.Append(";");
values = new StringBuilder ( "VALUES (" );
} //fin foreach
return sql.ToString ();
}

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

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

    DBCC CHECKIDENT('MyTable', RESEED, NEW_RESEED_VALUE)

然后你可以使用

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

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

将您的表复制到没有标识列的新表。

    select columns into newtable from yourtable

用new seed添加一个标识列到newtable，并将其作为主键

    ALTER TABLE tableName ADD id MEDIUMINT NOT NULL AUTO_INCREMENT KEY

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

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

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

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

SET IDENTITY_INSERT dbo.TableName ON
INSERT INTO dbo.TableName
(
TableId, ColumnName1, ColumnName2, ColumnName3
)
VALUES
(
TableId_Value, ColumnName1_Value, ColumnName2_Value, ColumnName3_Value
)


SET IDENTITY_INSERT dbo.TableName OFF

当使用Identity_Insert时，不要忘记包含列名，因为sql不允许你在没有指定列名的情况下进行插入

如果您特别需要将主键值更改为不同的数字(例如123 ->1123)。identity属性阻止更改PK值。Set Identity_insert将不起作用。如果您有级联删除，则不建议执行插入/删除操作(除非您关闭了引用完整性检查)。

EDIT:新版本的SQL不允许更改syscolumns实体，因此我的部分解决方案必须以艰难的方式完成。关于如何从主键中删除Identity，请参考这个SO: 从表中的列中删除标识符 . 这个脚本将关闭PK的标识:

***********************


sp_configure 'allow update', 1
go
reconfigure with override
go




update syscolumns set colstat = 0 --turn off bit 1 which indicates identity column
where id = object_id('table_name') and name = 'column_name'
go




exec sp_configure 'allow update', 0
go
reconfigure with override
go


***********************

现在，您可以进行更新。我写了一个简短的脚本来基于相同的列名编写所有的更新SQL(在我的情况下，我需要将CaseID增加1,000,000:

select
'update ['+c.table_name+'] SET ['+Column_Name+']=['+Column_Name+']+1000000'
from Information_Schema.Columns as c
JOIN Information_Schema.Tables as t ON t.table_Name=c.table_name and t.Table_Schema=c.table_schema and t.table_type='BASE TABLE'
where Column_Name like 'CaseID' order by Ordinal_position

最后，重新启用引用完整性，然后重新启用主键上的Identity列。

注意:我看到有些人在这些问题上问为什么。在我的例子中，我必须将来自第二个生产实例的数据合并到主DB中，这样才能关闭第二个实例。我只需要所有操作数据的PK/ fk不发生冲突。元数据fk是相同的。

我有类似的问题，我需要更新一些id，我所做的是(我需要增加他们10k):

set identity_insert YourTable ON


INSERT INTO YourTable
([ID]
,[something1]
,[something2]
,[something3])
SELECT
([ID] + 10000)
,[something1]
,[something2]
,[something3])
FROM YourTable
WHERE something1 = 'needs updeted id'
AND something2 = 'some other condition'


set identity_insert YourTable OFF


DELETE FROM YourTable
WHERE ID >= 'your old ID From'
AND ID <= 'Your old ID To'

就是这样。希望你能理解这个逻辑，在我的情况下，还有PK-FK键与其他表连接，这意味着我必须更新它们，然后才能从'YourTable'原始行中删除。

我知道这个问题已经有答案了，我只是想以SQL查询为例，

我做了以下事情:

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

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

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

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

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

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

DBCC CHECKIDENT('tableName', NORESEED);

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

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

不允许更新: 但是你可以

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

import:所有字段必须在insert into中声明 Sample: 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