我有SQL Server数据库，我想改变标识列，因为它开始了 有一个大数字10010，它与另一个表相关，现在我有200条记录，我想在记录增加之前修复这个问题。
更改或重置该列的最佳方法是什么?
当前回答
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不允许你在没有指定列名的情况下进行插入
其他回答
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 ();
}
您还可以使用SET IDENTITY INSERT来允许您将值插入到标识列中。
例子:
SET IDENTITY_INSERT dbo.Tool ON
GO
然后你可以在单位列中插入你需要的值。
如果您特别需要将主键值更改为不同的数字(例如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是相同的。
将您的表复制到没有标识列的新表。
select columns into newtable from yourtable
用new seed添加一个标识列到newtable，并将其作为主键
ALTER TABLE tableName ADD id MEDIUMINT NOT NULL AUTO_INCREMENT KEY
我做了以下事情:
将相关数据移动到临时存储中 更新主键/标识列值(删除和创建约束) 用新的外键值重新插入相关数据
我把我的解决方案包装在一个存储过程中:
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
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的
