source

테이블의 열에서 ID 제거

itover 2023. 4. 7. 21:12
반응형

테이블의 열에서 ID 제거

5GB 테이블(약 5억 행)이 있으며 열 중 하나에서 ID 속성을 삭제하려고 하는데 SSMS를 통해 삭제하려고 하면 타임아웃이 됩니다.

T-SQL로 할 수 있나요?

제거할 수 없습니다.IDENTITY사양을 설정합니다.

열 전체를 삭제하려면:

ALTER TABLE yourTable
DROP COLUMN yourCOlumn;

ALTER TABLE에 대한 자세한 내용은 이쪽

데이터를 보존할 필요가 있는 경우,IDENTITY컬럼, 다음 작업을 수행해야 합니다.

  • 새 열 만들기
  • 기존 데이터 전송IDENTITY새 열에 열 표시
  • 기존 삭제IDENTITY기둥.
  • 새 열의 이름을 원래 열 이름으로 변경

새 열을 추가 및 채우지 않고, 열을 재정렬하지 않고, 테이블에서 데이터를 변경하지 않기 때문에 다운타임이 거의 발생하지 않고 이 작업을 수행하려면 파티션 기능을 사용합니다(단, 파티션이 사용되지 않으므로 Enterprise Edition은 필요하지 않습니다).

  1. 이 테이블을 가리키는 모든 외부 키 제거
  2. 작성할 테이블을 스크립팅하고 'MyTable2', 'MyIndex2' 등 모든 항목의 이름을 변경합니다.ID 규격을 삭제합니다.
  3. 이제 2개의 "동일"한 테이블이 있어야 합니다.하나는 꽉 차 있고 다른 하나는 ID가 없는 빈 테이블입니다.
  4. 달려.ALTER TABLE [Original] SWITCH TO [Original2]
  5. 그러면 원래 테이블이 비어 있고 새 테이블에는 데이터가 포함됩니다.2개의 테이블의 메타데이터를(인스턴트) 전환했습니다.
  6. 원본(빈 테이블)을 삭제합니다.exec sys.sp_rename다양한 스키마 오브젝트의 이름을 원래 이름으로 변경한 후 외부 키를 다시 작성할 수 있습니다.

예를 들어 다음과 같습니다.

CREATE TABLE Original
(
  Id INT IDENTITY PRIMARY KEY
, Value NVARCHAR(300)
);
CREATE NONCLUSTERED INDEX IX_Original_Value ON Original (Value);

INSERT INTO Original
SELECT 'abcd'
UNION ALL 
SELECT 'defg';

다음을 수행할 수 있습니다.

--create new table with no IDENTITY
CREATE TABLE Original2
(
  Id INT PRIMARY KEY
, Value NVARCHAR(300)
);
CREATE NONCLUSTERED INDEX IX_Original_Value2 ON Original2 (Value);

--data before switch
SELECT 'Original', *
FROM Original
UNION ALL
SELECT 'Original2', *
FROM Original2;

ALTER TABLE Original SWITCH TO Original2;

--data after switch
SELECT 'Original', *
FROM Original
UNION ALL
SELECT 'Original2', *
FROM Original2;

--clean up 
IF NOT EXISTS (SELECT * FROM Original) DROP TABLE Original;
EXEC sys.sp_rename 'Original2.IX_Original_Value2', 'IX_Original_Value', 'INDEX';
EXEC sys.sp_rename 'Original2', 'Original', 'OBJECT';


UPDATE Original
SET Id = Id + 1;

SELECT *
FROM Original;

참고로, 점점 인기를 얻고 있기 때문에, 원래의 출처를 찾고 싶다고 생각하고 있었습니다(자신도 생각하지 않았습니다). 물론, 원래의 아이디어를 어디에서 찾았는지 잊어버린 지 오래입니다.아마 여기 있었을지도 모르지만, 이 답변보다 먼저 찾을 수 있는 것은 이것뿐입니다(시간 가는 줄, 소년, 소녀).https://social.technet.microsoft.com/wiki/contents/articles/17738.sql-server-quick-way-to-remove-the-identity-property.aspx

외부 키와 프라이머리 키의 제약으로 인해 문제가 생깁니다.따라서, 다음의 스크립트를 참조해 주세요.

먼저 임시 이름을 사용하여 중복 열을 만듭니다.

alter table yourTable add tempId int NOT NULL default -1;
update yourTable set tempId = id;

다음으로 프라이머리 키 제약의 이름을 가져옵니다.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'yourTable';

이제 열의 기본 키 제약 조건을 삭제해 보십시오.

ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;

외부 키가 있는 경우 오류가 발생하므로 외부 키 제약 조건을 해제합니다.나중에 제약을 다시 추가할 수 있도록 이 작업을 실행하는 테이블을 추적합니다!!!

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'otherTable';
alter table otherTable drop constraint fk_otherTable_yourTable;
commit;
..

모든 외부 키 제약 조건을 제거한 후에는 PK 제약 조건을 제거하고 해당 열을 삭제한 다음 임시 열의 이름을 변경하고 해당 열에 PK 제약 조건을 추가할 수 있습니다.

ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;
alter table yourTable drop column id;
EXEC sp_rename 'yourTable.tempId', 'id', 'COLUMN';
ALTER TABLE yourTable ADD CONSTRAINT PK_yourTable_id PRIMARY KEY (id) 
commit;

마지막으로 다음에 FK 구속조건을 추가합니다.

alter table otherTable add constraint fk_otherTable_yourTable foreign key (yourTable_id) references yourTable(id);
..

엘핀!

나도 똑같은 문제가 있었어GUI를 사용하지 않고 SSMS에서 4개의 문을 사용했는데 매우 빨랐습니다.

  • 새 열 만들기

    alter table users add newusernum int;

  • 값 복사

    update users set newusernum=usernum;

  • 오래된 컬럼을 드롭합니다.

    alter table users drop column usernum;

  • 새 열의 이름을 이전 열 이름으로 변경합니다.

    EXEC sp_RENAME 'users.newusernum' , 'usernum', 'COLUMN';

다음 스크립트는 'Id'라는 이름의 열에 대한 ID 필드를 제거합니다.

도움이 됐으면 좋겠다.

BEGIN TRAN
BEGIN TRY
    EXEC sp_rename '[SomeTable].[Id]', 'OldId';

    ALTER TABLE [SomeTable] ADD Id int NULL

    EXEC ('UPDATE [SomeTable] SET Id = OldId')

    ALTER TABLE [SomeTable] NOCHECK CONSTRAINT ALL

    ALTER TABLE [SomeTable] DROP CONSTRAINT [PK_constraintName];
    ALTER TABLE [SomeTable] DROP COLUMN OldId
    ALTER TABLE [SomeTable] ALTER COLUMN [Id] INTEGER NOT NULL
    ALTER TABLE [SomeTable] ADD CONSTRAINT PK_JobInfo PRIMARY KEY (Id)

    ALTER TABLE [SomeTable] CHECK CONSTRAINT ALL

    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN   
    SELECT ERROR_MESSAGE ()
END CATCH

벨로우 코드는 정상 작동하며, 우리가 식별 열 이름을 모를 때 사용됩니다.

해야 합니다.Invoice_DELETED에는 이렇게 쓰겠습니다.

insert into Invoice_DELETED select * from Invoice where ...


SELECT t1.*
INTO Invoice_DELETED
FROM Invoice t1
LEFT JOIN Invoice ON 1 = 0
--WHERE t1.InvoiceID = @InvoiceID

상세한 것에 대하여는, https://dba.stackexchange.com/a/138345/101038 를 참조해 주세요.

ALTER TABLE tablename add newcolumn int
update tablename set newcolumn=existingcolumnname
ALTER TABLE tablename DROP COLUMN existingcolumnname;
EXEC sp_RENAME 'tablename.oldcolumn' , 'newcolumnname', 'COLUMN'

그러나 위의 코드는 기본 키와 외부 키 관계가 없는 경우에만 작동합니다.

SQL Server에서는 다음과 같이 ID 삽입을 켜거나 끌 수 있습니다.

아이덴티티 설정_table_name ON 삽입

-- 여기서 쿼리를 실행합니다.

아이덴티티 설정_INSERT table_name OFF

나랑 같은 문제를 가진 사람을 위해서요한 번만 더 삽입하고 싶으면 이렇게 하세요.

두 개의 열이 있는 표가 있다고 가정합니다.

ID Identity (1,1) | Name Varchar

ID가 4인 행을 삽입하려고 합니다.그래서 3으로 다시 붙였으니 다음 것은 4입니다.

DBCC CHECKIDENT([YourTable], RESEED, 3)

인서트 작성하다

INSERT  INTO [YourTable]
        ( Name )
VALUES  ( 'Client' )

그리고 당신의 씨앗을 가장 높은 아이디로 돌려놓으세요, 15라고 합시다.

DBCC CHECKIDENT([YourTable], RESEED, 15)

알았어!

저도 같은 요건이 있었습니다.이 방법을 사용해 보세요.개인적으로 추천합니다.테이블을 수동으로 설계하고 스크립트를 생성해 주세요.다음은 오래된 테이블의 이름과 백업에 관한 제약 조건의 이름을 변경하는 것입니다.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SI_Provider_Profile
    DROP CONSTRAINT DF_SI_Provider_Profile_SIdtDateTimeStamp
GO
ALTER TABLE dbo.SI_Provider_Profile
    DROP CONSTRAINT DF_SI_Provider_Profile_SIbHotelPreLoaded
GO
CREATE TABLE dbo.Tmp_SI_Provider_Profile
    (
    SI_lProvider_Profile_ID int NOT NULL,
    SI_lSerko_Integrator_Token_ID int NOT NULL,
    SI_sSerko_Integrator_Provider varchar(50) NOT NULL,
    SI_sSerko_Integrator_Profile varchar(50) NOT NULL,
    SI_dtDate_Time_Stamp datetime NOT NULL,
    SI_lProvider_ID int NULL,
    SI_sDisplay_Name varchar(10) NULL,
    SI_lPurchased_From int NULL,
    SI_sProvider_UniqueID varchar(255) NULL,
    SI_bHotel_Pre_Loaded bit NOT NULL,
    SI_sSiteName varchar(255) NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_SI_Provider_Profile SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_SI_Provider_Profile ADD CONSTRAINT
    DF_SI_Provider_Profile_SIdtDateTimeStamp DEFAULT (getdate()) FOR SI_dtDate_Time_Stamp
GO
ALTER TABLE dbo.Tmp_SI_Provider_Profile ADD CONSTRAINT
    DF_SI_Provider_Profile_SIbHotelPreLoaded DEFAULT ((0)) FOR SI_bHotel_Pre_Loaded
GO
IF EXISTS(SELECT * FROM dbo.SI_Provider_Profile)
        EXEC('INSERT INTO dbo.Tmp_SI_Provider_Profile (SI_lProvider_Profile_ID, SI_lSerko_Integrator_Token_ID, SI_sSerko_Integrator_Provider, SI_sSerko_Integrator_Profile, SI_dtDate_Time_Stamp, SI_lProvider_ID, SI_sDisplay_Name, SI_lPurchased_From, SI_sProvider_UniqueID, SI_bHotel_Pre_Loaded, SI_sSiteName)
        SELECT SI_lProvider_Profile_ID, SI_lSerko_Integrator_Token_ID, SI_sSerko_Integrator_Provider, SI_sSerko_Integrator_Profile, SI_dtDate_Time_Stamp, SI_lProvider_ID, SI_sDisplay_Name, SI_lPurchased_From, SI_sProvider_UniqueID, SI_bHotel_Pre_Loaded, SI_sSiteName FROM dbo.SI_Provider_Profile WITH (HOLDLOCK TABLOCKX)')
GO

-- Rename the primary key constraint or unique key In SQL Server constraints such as primary keys or foreign keys are objects in their own right, even though they are dependent upon the "containing" table.
EXEC sp_rename 'dbo.SI_Provider_Profile.PK_SI_Provider_Profile', 'PK_SI_Provider_Profile_Old';
GO
-- backup old table in case of 
EXECUTE sp_rename N'dbo.SI_Provider_Profile', N'SI_Provider_Profile_Old', 'OBJECT'
GO

EXECUTE sp_rename N'dbo.Tmp_SI_Provider_Profile', N'SI_Provider_Profile', 'OBJECT'
GO

ALTER TABLE dbo.SI_Provider_Profile ADD CONSTRAINT
    PK_SI_Provider_Profile PRIMARY KEY NONCLUSTERED 
    (
    SI_lProvider_Profile_ID
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT TRANSACTION

ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME DROP IDENTITY);

언급URL : https://stackoverflow.com/questions/8230257/remove-identity-from-a-column-in-a-table

반응형

'source' 카테고리의 다른 글

쉼표로 구분된 값을 열로 분할하는 방법  (0) 2023.04.07
SQL Server에서 특정 날짜보다 큰 모든 날짜를 조회하려면 어떻게 해야 합니까?  (0) 2023.04.07
단일 사용자 모드 종료  (0) 2023.04.07
sql 쿼리: 두 테이블 간의 차이를 반환합니다.  (0) 2023.04.07
데이터를 다른 테이블로 복사  (0) 2023.04.07

'source'의 다른글

  • 현재글테이블의 열에서 ID 제거

관련글

댓글

티스토리툴바