복붙노트

[SQL] 테이블의 열에서 신원 제거

SQL

테이블의 열에서 신원 제거

우리는 5GB 용량 표 (약 500 만 행)을 가지고 있고 우리는 열 중 하나에 ID 속성을 제거하려면,하지만 우리는 SSMS를 통해이 작업을 수행하려고 할 때 - 그것은 시간을.

이것은 T-SQL을 통해 수행 할 수 있습니까?

해결법

  1. ==============================

    1.설정하면 당신은 IDENTITY 사양을 제거 할 수 없습니다.

    설정하면 당신은 IDENTITY 사양을 제거 할 수 없습니다.

    전체 열을 제거하려면 :

    ALTER TABLE yourTable
DROP COLUMN yourCOlumn;

    여기 ALTER 표에 대한 정보

    당신이 데이터를 유지하지만, IDENTITY 열을 제거해야하는 경우, 당신은해야합니다 :

  2. ==============================

    2.어떤 파티션이 사용되지 않기 때문에 당신이하고 중단 시간없이 데이터가 테이블에 변경되지 않기 때문에로, 열을 재정렬하지 않고, 추가하고 새 열을 채우지 않고이 작업을 수행하려면,하자가 기능을 분할하여 마법을 (하지만 당신은 돈 ' t)는 엔터프라이즈 버전이 필요합니다 :

    어떤 파티션이 사용되지 않기 때문에 당신이하고 중단 시간없이 데이터가 테이블에 변경되지 않기 때문에로, 열을 재정렬하지 않고, 추가하고 새 열을 채우지 않고이 작업을 수행하려면,하자가 기능을 분할하여 마법을 (하지만 당신은 돈 ' t)는 엔터프라이즈 버전이 필요합니다 :

    예를 들어, 주어진 :

    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;
  3. ==============================

    3.이것은 그래서 여기에 당신의 방법에 당신을 도울 몇 가지 스크립트의 외국 및 기본 키 제약으로 혼란을 가져옵니다

    이것은 그래서 여기에 당신의 방법에 당신을 도울 몇 가지 스크립트의 외국 및 기본 키 제약으로 혼란을 가져옵니다

    첫째, 임시 이름을 가진 중복 된 열을 생성 :

    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;

    당신이 외래 키가있는 경우이므로 외래 키 제약 조건을 삭제 그래서 만약, 그것은 실패합니다. 당신 RUN이를 위해 그래서 당신은 제약 다시 나중에 추가 할 수있는 테이블의 KEEP 트랙!

    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);
..

    끝!

  4. ==============================

    4.난 그냥이 같은 문제가 없었다. 4 SSMS 대신 GUI 사용에 문 그리고 그것은 매우 빨랐다.

    난 그냥이 같은 문제가 없었다. 4 SSMS 대신 GUI 사용에 문 그리고 그것은 매우 빨랐다.

  5. ==============================

    5.다음 스크립트는 '아이디'라는 이름의 열에 대한 신원 필드를 제거

    다음 스크립트는 '아이디'라는 이름의 열에 대한 신원 필드를 제거

    희망이 도움이.

    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
  6. ==============================

    6.우리는 식별 컬럼 이름을 모르는 잘으로 작업 벨로 코드입니다.

    우리는 식별 컬럼 이름을 모르는 잘으로 작업 벨로 코드입니다.

    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

  7. ==============================

    7.

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

    그러나 위의 코드에만 작동없이 기본 - 외부 키 관계에있는 경우

  8. ==============================

    8.나도 같은 요구 사항을 가지고, 당신은 내가 개인적으로 당신을 추천 수동으로 테이블을 디자인하고 스크립트를 생성하고, 또한 제가 아래에했던 이전 테이블의 이름을 변경했다 및 백업에 대한 제약주십시오 방법을 시도 할 수 있습니다.

    나도 같은 요구 사항을 가지고, 당신은 내가 개인적으로 당신을 추천 수동으로 테이블을 디자인하고 스크립트를 생성하고, 또한 제가 아래에했던 이전 테이블의 이름을 변경했다 및 백업에 대한 제약주십시오 방법을 시도 할 수 있습니다.

    /* 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
  9. ==============================

    9.SQL 서버에서 당신과 같은 신원 삽입 해제 할 수 있습니다 :

    SQL 서버에서 당신과 같은 신원 삽입 해제 할 수 있습니다 :

    SET IDENTITY_INSERT의 TABLE_NAME의 ON

    - 여기에 쿼리를 실행

    SET IDENTITY_INSERT의 TABLE_NAME의 OFF

  10. ==============================

    10.그냥 내가 한 같은 문제가 누군가. 당신은 당신이 뭔가를 할 수있는 한 번만 약간의 삽입을하십시오.

    그냥 내가 한 같은 문제가 누군가. 당신은 당신이 뭔가를 할 수있는 한 번만 약간의 삽입을하십시오.

    두 개의 열이있는 테이블이 있다고 가정하자

    ID Identity (1,1) | Name Varchar

    그리고 ID를 가진 행을 삽입 할 = 4. 다음 중 하나가 4 있도록 3에 seed를 다시 그래서

    DBCC CHECKIDENT([YourTable], RESEED, 3)

    삽입을 확인

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

    그리고 가정 해는 15 수, 가장 높은 ID로 종자 다시 얻을

    DBCC CHECKIDENT([YourTable], RESEED, 15)

    끝난!

    11. from https://stackoverflow.com/questions/8230257/remove-identity-from-a-column-in-a-table by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 날짜 사이 SQL 선택  (0) 2020.03.21
[SQL] 각 그룹의 최대 값을 선택  (0) 2020.03.21
[SQL] MySQL의 업데이트 쿼리에 '필드 목록'오류 알 수없는 열  (0) 2020.03.21
[SQL] OPENQUERY에서 매개 변수를 포함  (0) 2020.03.21
[SQL] 먼저 특정 필드 값에 의해 주문  (0) 2020.03.21
,

티스토리툴바