我有SQL Server数据库,我想改变标识列,因为它开始了 有一个大数字10010,它与另一个表相关,现在我有200条记录,我想在记录增加之前修复这个问题。
更改或重置该列的最佳方法是什么?
我有SQL Server数据库,我想改变标识列,因为它开始了 有一个大数字10010,它与另一个表相关,现在我有200条记录,我想在记录增加之前修复这个问题。
更改或重置该列的最佳方法是什么?
当前回答
您可以使用以下代码创建一个新表。
SELECT IDENTITY (int, 1, 1) AS id, column1, column2
INTO dbo.NewTable
FROM dbo.OldTable
然后删除旧的db,并将新db重命名为旧db的名称。注意:columnn1和column2表示旧表中希望保留在新表中的所有列。
其他回答
如果您特别需要将主键值更改为不同的数字(例如123 -> 1123)。identity属性阻止更改PK值。Set Identity_insert将不起作用。如果您有级联删除,则不建议执行插入/删除操作(除非您关闭了引用完整性检查)。
编辑:新版本的SQL不允许更改syscolumns实体,因此我的部分解决方案必须以艰难的方式完成。关于如何从主键中删除Identity,请参考这个SO: 从表中的列中删除Identity 这个脚本将在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
***********************
接下来,您可以设置关系,以便它们更新外键引用。否则你需要关闭关系强制执行。这个SO链接展示了如何: 如何使用T-SQL临时禁用外键约束?
现在,您可以进行更新。我写了一个简短的脚本来基于相同的列名编写所有的更新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是相同的。
尝试使用DBCC CHECKIDENT:
DBCC CHECKIDENT ('YourTable', RESEED, 1);
您可以使用以下代码创建一个新表。
SELECT IDENTITY (int, 1, 1) AS id, column1, column2
INTO dbo.NewTable
FROM dbo.OldTable
然后删除旧的db,并将新db重命名为旧db的名称。注意:columnn1和column2表示旧表中希望保留在新表中的所有列。
您不能更新标识列。 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
我做了以下事情:
将相关数据移动到临时存储中 更新主键/标识列值(删除和创建约束) 用新的外键值重新插入相关数据
我把我的解决方案包装在一个存储过程中:
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