SQL Server에서 ID 열을 업데이트하는 방법
큰 번호의 SQL Server ID로 되었기 때문에 .10010을 하다200파운드레코드가 증가하기 전에 이 문제를 수정하고 싶습니다.
이 열을 변경하거나 재설정하는 가장 좋은 방법은 무엇입니까?
ID 열은 업데이트할 수 없습니다.
업데이트 문이 있는 다른 열에서 수행할 수 있는 작업과 달리 SQL Server에서는 ID 열을 업데이트할 수 없습니다.
비슷한 종류의 요구사항을 달성할 수 있는 몇 가지 대안이 있지만.
- 새 레코드에 대해 ID 열 값을 업데이트해야 하는 경우
테이블의 현재 ID 값을 확인하고 필요한 경우 ID 값을 변경하는 DBCC CHECKIDENT를 사용합니다.
DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)
- 기존 레코드의 ID 열 값을 업데이트해야 하는 경우
ID 사용_INSERT: 명시적 값을 테이블의 ID 열에 삽입할 수 있습니다.
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
질문을 맞췄다면 이렇게 하고 싶다.
update table
set identity_column_name = some value
그것을 되지 않습니다. 씀드드, 것것 let let let let let let let let let 가 있을 수 때문입니다. 그리고 그것을 사용하는 것은 권장되지 않을 수도 있습니다.foreign key관련지어져 있습니다.
이렇게. 도 꼭 한 번 해 주세요.back-up
스텝 1-테이블의 설계 뷰 선택
2단계 - ID 열 끄기
, 그럼 에는 '어울리지 않다'를 사용하시면 .update
, 이제redo및 [ on ]의 [Turn on identity column]의 [ID]의 [Turn]를 합니다.
할 필요가 있다
set identity_insert YourTable ON
그런 다음 행을 삭제하고 다른 ID로 다시 삽입합니다.
삽입이 완료되면 identity_insert를 꺼야 합니다.
set identity_insert YourTable OFF
--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
다음을 사용해 보십시오.
DBCC CHECKIDENT ('YourTable', RESEED, 1);
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
ID_를 사용하는 경우sql에서는 열 이름을 지정하지 않고 삽입할 수 없으므로 열 이름을 포함하는 것을 잊지 마십시오.
DBCC CHECKIDENT(table_name, RESEED, value)
table_name = 값을 재설정할 테이블을 제공합니다.
value = ID 열을 1로 시작하려면 초기값이 0이어야 합니다.
ID 열 없이 새 테이블에 테이블을 복사합니다.
select columns into newtable from yourtable
ID 열을 새 시드를 사용하여 새 테이블에 추가하고 기본 키로 만듭니다.
ALTER TABLE tableName ADD id MEDIUMINT NOT NULL AUTO_INCREMENT KEY
를 사용하여 ID 열에 값을 삽입할 수도 있습니다.
예:
SET IDENTITY_INSERT dbo.Tool ON
GO
그런 다음 필요한 값을 ID 열에 삽입할 수 있습니다.
몇 가지 ID를 업데이트해야 하는 것과 같은 문제가 있었습니다(그 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'
이치노논리를 합니다. 다른 키 이 키를 에야 이이논 hope from from from from from 에서 삭제할 수 .저의 경우는, 다른 테이블과의 PK-FK 키 접속도 있었습니다.그것은, 그 테이블들을 갱신하고 나서, 다음에 삭제할 수 있게 되었습니다.'YourTable'원래 행
이에 대한 답변이 이미 있다는 것을 알고 있습니다. SQL 쿼리를 예로 남겨두고 싶습니다.
지금까지 본 것처럼 몇 가지 방법이 있지만, 가장 빠르고 좋은 방법은 다음과 같습니다.
identity 열에 등록된 열과 반드시 동일하지는 않은 카운터가 있습니다. 다음 SQL 명령을 사용하여 이 카운터의 값을 확인할 수 있습니다.
DBCC CHECKIDENT('tableName', NORESEED);
그러면 아이덴티티 컬럼을 편집할 수 없지만, 카운터를 필요한 번호만큼 빼고 다시 등록하는 것이 좋습니다.카운터를 다시 설치하려면 다음 명령을 사용합니다.
DBCC CHECKIDENT('tableName', RESEED, desiredNumber);
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
그러나 위의 코드는 기본 키와 외부 키 관계가 없는 경우에만 작동합니다.
Command Builder를 사용하여 C# 프로그래머를 위한 완벽한 솔루션
먼저 다음 사실을 알아야 합니다.
- 어떤 경우에도 ID 열은 수정할 수 없으므로 행을 삭제하고 새 ID로 다시 추가해야 합니다.
- 열에서 ID 속성을 제거할 수 없습니다(열로 제거해야 함).
- .net의 커스텀명령어 빌더는 항상 ID 열을 건너뛰기 때문에 이 목적으로 사용할 수 없습니다.
그래서 그걸 알게 되면 해야 할 일은SQL Insert 문을 직접 프로그래밍하거나 사용자가 소유한 insert 명령 작성기를 프로그래밍합니다.아니면 내가 프로그램한 걸 쓰던가DataTable을 지정하면 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 ();
}
업데이트는 허용되지 않지만 가능합니다.
- 올바른 키로 새 데이터 삽입
- 레지 삭제
import: 모든 필드를 샘플에 삽입하여 선언해야 합니다.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
먼저 DBCC를 사용하여 해결한 후 insert를 사용하여 해결했습니다.예를 들어 테이블이
먼저 테이블의 새로운 현재 ID 값을 NEW_RESEED_로 설정합니다.가치
MyTable {IDCol, colA, colB}
DBCC CHECKIDENT('MyTable', RESEED, NEW_RESEED_VALUE)
그 후 를 사용할 수 있습니다.
insert into MyTable (colA, ColB) select colA, colB from MyTable
NEW_RESEED_로 시작하는 새로운 IDCol 값을 사용하여 모든 레코드가 복제됩니다.VALUE. 그러면 외부 키 참조를 제거/이동한 후 더 높은 ID 값 중복 행을 제거할 수 있습니다.
다음 코드를 사용하여 새 테이블을 만들 수 있습니다.
SELECT IDENTITY (int, 1, 1) AS id, column1, column2
INTO dbo.NewTable
FROM dbo.OldTable
그런 다음 이전 db를 삭제하고 새 db의 이름을 이전 db 이름으로 변경합니다.주의: 이 column1과 column2는 이전 테이블에서 새 테이블에 유지할 모든 열을 나타냅니다.
다음을 수행했습니다.
- 관련 데이터를 임시 저장소로 이동
- 기본 키/아이덴티티 열 값 업데이트(제약 조건 삭제 및 생성)
- 새로운 외부 키 값을 사용하여 관련 데이터 재삽입
스토어드 프로시저로 솔루션을 포장했습니다.
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
이 문제는 제가 머지를 하고 있던 아이디를 갱신하는 머지를 했기 때문에 발생했습니다.
효과가 없는 예(광대 참고)아이디:
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;
효과가 있는 예(광대 참고)아이디:
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;
이 솔루션은 ALTER TABLE SWITCH 문에 의존합니다.이 매뉴얼은 파티션에 매우 중점을 두고 있지만 파티션 없이 여기에 적용할 수 있습니다.테이블 간에 데이터를 매우 빠르게 이동할 수 있습니다.많은 제약사항이 있지만 그 목록에 대한 한 가지 주목할 만한 예외는 다음과 같습니다.
IDENTY 속성은 고려되지 않습니다.
즉, ALTER TABLE SWITCH 문을 사용하여 대상이 소스와 동일한 방식으로 정의된 ID가 없는 경우에도 한 테이블에서 다른 테이블로 데이터를 이동할 수 있습니다.
이 시나리오에서는 ID 열이 외부 키로 참조되지 않았습니다.그렇지 않은 경우에도 이 솔루션을 사용할 수 있지만 참조 테이블의 외부 키 값을 추가로 업데이트해야 합니다.
- 다음 절차를 수행하여 해당 테이블의 스크립트를 조정합니다.
- 새(임시) 이름을 선택합니다.
- 모든 테이블 제약조건에 고유한 이름을 지정합니다.
- ID 정의를 삭제합니다.
- 다음 문을 실행하여 기존 테이블에서 위에서 작성한 새로운 임시 테이블로 데이터를 이동합니다.
ALTER TABLE <EXISTING> SWITCH TO <NEW>;
- 테이블 이름을 삽입하고 필요에 따라 열 이름을 조정한 후 다음 문을 실행합니다.그러면 ID가 압축되어 1부터 공백 없이 시작됩니다.이것은 아마도 완료하는 데 가장 오랜 시간이 걸리는 단계일 것입니다.
WITH ids as
(
SELECT
ID,
ROW_NUMBER() OVER (ORDER BY ID) [NewID]
FROM
<NEW>
)
UPDATE
ids
SET
ID = ids.[NewID];
- 다음 문을 실행하여 데이터를 임시 테이블에서 기존 테이블로 다시 스왑합니다.
ALTER TABLE SWITCH <NEW> TO <EXISTING>;
- 새로운 ID 값이 테이블 상단에서 이어지도록 ID 열을 다시 설치했다.
DBCC CHECKIDENT ('dbo.FeeItem', RESEED, 0); --Set it to a value that is too low.
DBCC CHECKIDENT ('dbo.FeeItem', RESEED); --Will automatically update it to the maximum value that currently exists in the table.
DBCC CHECKIDENT ('dbo.FeeItem', NORESEED); --Will describe how the identity ends up so that we can check it.
- 이제 새 임시 테이블은 내려놓으세요
프라이머리 키의 값을 다른 번호(예:123 -> 1123)로 변경할 필요가 있는 경우.ID 속성은 PK 값 변경을 차단합니다.Identity_insert 설정이 작동하지 않습니다.단계적 삭제가 있는 경우(참조 무결성 검사를 해제하지 않는 한) 삽입/삭제는 권장되지 않습니다.
편집: 새로운 버전의 SQL에서는 syscolumns 엔티티를 변경할 수 없기 때문에 솔루션의 일부는 어려운 방법으로 변경해야 합니다.대신 프라이머리 키에서 ID를 삭제하는 방법에 대해서는 다음 SO를 참조하십시오.테이블의 열에서 ID 제거 이 스크립트는 PK에서 ID를 해제합니다.
***********************
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을 동일한 열 이름을 기반으로 작성하기 위해 짧은 스크립트를 작성했습니다(내 경우 Case를 늘려야 했습니다).아이디: 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는 동일합니다.
언급URL : https://stackoverflow.com/questions/19155775/how-to-update-identity-column-in-sql-server
'programing' 카테고리의 다른 글
| 왼쪽의 varchar를 특정 길이로 패딩하는 가장 효율적인 T-SQL 방법? (0) | 2023.04.08 |
|---|---|
| SQL Server에서 레코드를 삭제한 후 ID 시드 재설정 (0) | 2023.04.08 |
| EPPlus의 자동 열 폭 (0) | 2023.04.08 |
| 낮은 버전의 SQL Server 데이터베이스 백업 복원 (0) | 2023.04.08 |
| 여러 열에 고유한 제약 조건 (0) | 2023.04.08 |