programing

기존 열에 ID 추가

topblog 2023. 4. 8. 07:52
반응형

기존 열에 ID 추가

테이블의 기본 키를 ID 열로 변경해야 합니다. 테이블에는 이미 여러 행이 있습니다.

1부터 순서대로 ID를 정리하고 테스트 데이터베이스에서 정상적으로 동작하도록 하는 스크립트가 있습니다.

ID 속성을 가지도록 열을 변경하는 SQL 명령은 무엇입니까?

ID에 대해 기존 열을 변경할 수 없습니다.

두 가지 옵션이 있습니다.

  1. ID를 사용하여 새 테이블을 만들고 기존 테이블을 삭제합니다.

  2. ID를 사용하여 새 열을 만들고 기존 열을 삭제합니다.

접근법 1. (새로운 표)여기서 새로 생성된 ID 열의 기존 데이터 값을 유지할 수 있습니다.'존재하지 않는 경우'가 충족되지 않으면 모든 데이터가 손실되므로 조건도 반드시 삭제해야 합니다.

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

접근법 2(새 열) 새로 생성된 ID 열의 기존 데이터 값을 유지할 수 없습니다. ID 열에는 번호 시퀀스가 유지됩니다.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

상세한 것에 대하여는, 다음의 Microsoft SQL Server Forum 의 투고를 참조해 주세요.

열을 ID로 변경하는 방법(1,1)

SQL 2005 이상에서는 테이블의 데이터 페이지를 변경하지 않고 이 문제를 해결하는 방법이 있습니다.이것은 모든 데이터 페이지를 터치하는 데 몇 분 또는 몇 시간이 걸릴 수 있는 큰 테이블에서 중요합니다.또한 ID 열이 기본 키이거나 클러스터 또는 비클러스터된 인덱스의 일부이거나 더 단순한 "열 추가/제거/이름 변경" 솔루션을 중단시킬 수 있는 기타 gotchas의 일부인 경우에도 이 트릭이 작동합니다.

SQL Server의 ALTER TABLE을 사용할 수 있습니다.SWITCH 스테이트먼트: 데이터를 변경하지 않고 테이블의 스키마를 변경합니다.즉, IDENTY 컬럼 없이 동일한 테이블스키마를 가진 ID로 테이블을 치환할 수 있습니다.IDENTY를 기존 열에 추가하는 경우에도 동일한 트릭이 작동합니다.

보통 테이블 변경...SWITCH 는 파티션 테이블 내의 풀 파티션을 새로운 빈 파티션으로 효율적으로 치환하기 위해 사용됩니다.그러나 분할되지 않은 테이블에서도 사용할 수 있습니다.

이 방법을 사용하여 25억 행 테이블 열을 IDENTY에서 IDENTY 이외의 열로 변환하고(IDENTY 이외의 열에 대해 쿼리 계획이 더 잘 작동하는 수 시간 쿼리를 실행하기 위해), IDENTY 설정을 5초 이내에 다시 복원했습니다.

다음은 작동 방식에 대한 코드 샘플입니다.

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );
  
 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');
  
 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );
  
 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';
  
 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test;

이것은 분명히 다른 답변의 솔루션보다 더 많이 관련되어 있지만, 테이블이 크면 진정한 구명책이 될 수 있습니다.몇 가지 주의사항이 있습니다.

  • 제가 알기로는 이 방법으로 테이블의 컬럼을 변경할 수 있는 것은 아이덴티티뿐입니다.열 추가/제거, nullability 변경 등은 허용되지 않습니다.
  • 전환하기 전에 암호화 키를 떨어뜨리고 나중에 복원해야 합니다.
  • WITH SCHEMABIND 함수, 뷰 등에 대해서도 동일합니다.
  • 새 테이블의 인덱스는 정확히 일치해야 합니다(같은 열, 같은 순서 등).
  • 기존 테이블과 새 테이블은 동일한 파일 그룹에 있어야 합니다.
  • SQL Server 2005 이후에서만 동작
  • 이전에는 이 트릭이 SQL Server의 Enterprise 에디션 또는 Developer 에디션에서만 작동한다고 생각했지만(파티션은 Enterprise 버전과 Developer 버전에서만 지원되므로), Mason G. Zhwiti는 아래의 코멘트에서 SQL Standard Edition에서도 작동한다고 말합니다.이는 엔터프라이즈 또는 개발자에 대한 제한이 ALTER TABLE에는 적용되지 않는다는 것을 의미합니다.스위치

TechNet에는 위의 요건에 대한 자세한 기사가 게재되어 있습니다.

업데이트 - Eric Wu가 이 솔루션에 대한 중요한 정보를 추가한 코멘트를 아래에 게재했습니다.여기에 복사하여 주목도를 높입니다.

여기에 언급할 가치가 있는 또 다른 주의사항이 있습니다.새 테이블은 이전 테이블에서 데이터를 수신하고 ID 패턴에 따라 모든 새 행이 삽입되지만 해당 열이 기본 키일 경우 1부터 시작되고 끊어질 수 있습니다.「」의 실행을 한다.DBCC CHECKIDENT('<newTableName>')전환 직후에 표시됩니다.상세한 것에 대하여는, msdn.microsoft.com/en-us/library/ms176057.aspx 를 참조해 주세요.

하게 확장되고 경우(IDENTY 추가와 행 는, 「IDENTY」(IDENTY 추가) 대신에 「」(IDENTY)를 사용해 주세요.DBCC CHECKIDENT의 ID 내의 할 수 ID가 크다).IDENTITY (2435457, 1) 둘 다 수 요.ALTER TABLE...SWITCH 및DBCC CHECKIDENT이 값을 수동으로 설정하는 것이 더 쉽고 안전할 것으로 생각됩니다.

되지 않은 의 ETL이하지 않으므로 (ETL은)는 발생하지 .DBCC CHECKIDENT괜찮아요.

열을 IDENTY 열로 변경할 수 없습니다.이 경우 get-go에서 ID로 정의된 새 열을 만든 다음 이전 열을 삭제하고 새 열을 이전 이름으로 이름을 변경해야 합니다.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

마크

여기에서는 SQL SERVER – 열에 ID 속성 추가/삭제라는 멋진 솔루션을 소개합니다.

SQL Manager에서 테이블을 수동으로 편집하려면 ID를 전환하고 변경 내용을 저장하지 마십시오. 변경 내용을 저장할 스크립트를 표시하고 복사한 후 나중에 사용합니다.

변경 테이블과 관련된 모든 외부 키, 인덱스 등이 포함되어 있기 때문에 시간을 크게 절약할 수 있습니다.수동으로 쓰는 중...절대 안 돼.

ID 대신 SEQUENCE를 사용하는 것을 고려해 주십시오.

sql server 2014에서는 (아래 버전은 잘 모르겠습니다)시퀀스를 사용하여 간단하게 이 작업을 수행할 수 있습니다.

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

From here: 컬럼의 디폴트값으로 시퀀스합니다.

간단한 설명

sp_RENAME을 사용하여 기존 열 이름 변경

EXEC sp_RENAME '테이블_이름'.Existing_ColumnName', 'New_ColumnName', 'COLUMN'

이름 변경 예시:

기존 열 사용자ID 이름이 Old User로 변경됨아이디

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

그런 다음 alter 쿼리를 사용하여 기본 키 및 ID 값으로 설정하는 새 열을 추가합니다.

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Set Primary 키의 예

새로 생성된 열 이름은 사용자입니다.아이디

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

그런 다음 이름 변경 열을 놓습니다.

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

이름 변경 삭제 열 예제

ALTER TABLE Users DROP COLUMN OldUserID

이제 테이블의 기존 열에 기본 키와 ID를 추가합니다.

저는 우연히 DBA 없이 DBA 권한을 얻을 수 없는 팀에 합류한 Java 개발자입니다.스키마 전체를 2개의 데이터베이스 간에 이동해야 했기 때문에 DBA가 없으면 스크립트를 실행하여 실행해야 했습니다.관리자 권한이 없었기 때문에 SQL Server 2008에서는 GUI를 사용할 수 없었습니다.

모든 것이 문제없이 이동되었습니다만, 새로운 schema.table에서 스토어드 프로시저를 실행했을 때 테이블 내의 ID 필드가 없어졌습니다.테이블을 작성한 스크립트를 다시 확인했는데 테이블이 있었는데 스크립트를 실행했을 때 SQL Server가 취득하지 않았습니다.나중에 DBA로부터 같은 문제를 경험했다는 말을 들었습니다.

어쨌든 SQL Server 2008의 경우, 이 문제를 해결하기 위해 취한 조치들이 모두 성공했기 때문에 누군가에게 도움이 되기를 바라며 이 글을 여기에 올렸습니다.FK가 다른 테이블에 종속되어 있기 때문에 이 작업을 더 어렵게 만들었습니다.

이 쿼리를 사용하여 ID가 실제로 누락되었는지 확인하고 테이블에 대한 종속성을 확인했습니다.

1) 표에서 통계 정보 검색:

exec sp_help 'dbo.table_name_old';

2) 중복된 동일한 새 테이블을 만듭니다.단, 이전에 존재했던 PK 필드에 ID 필드를 추가합니다.

3) 데이터를 이동할 ID를 비활성화합니다.

SET IDENTITY_INSERT dbo.table_name ON

4.) 데이터를 전송합니다.

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT 
field1, field2, etc...
FROM 
dbo.table_name_old;

5.) 데이터가 있는지 확인합니다.

SELECT * FROM dbo.table_name_new

6.) ID를 다시 활성화합니다.

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) 이것은 원래 테이블이 어떤 테이블을 참조하는지 확인하기 위해 모든 FK 관계를 취득하기 위해 찾은 최고의 스크립트이며, 많은 것을 발견했기 때문에 키퍼입니다.

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
   ORDER BY ReferenceTableName;

8.) 이 다음 단계를 수행하기 전에 관련된 모든 테이블의 모든 PK 및 FK 스크립트가 있는지 확인하십시오.

9.) 각 키를 오른쪽 클릭하여 SQL Server 2008을 사용하여 스크립팅할 수 있습니다.

10.) 다음 구문을 사용하여 의존관계 테이블에서 FK를 삭제합니다.

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

11.) 원래 테이블을 드롭합니다.

DROP TABLE dbo.table_name_old;

13.) 다음 단계는 스텝9에서 SQL Server 2008에서 작성한 스크립트에 의존합니다.

--PK를 새 테이블에 추가합니다.

--새로운 테이블에 FK를 추가합니다.

FK의 뒷면을 의존관계 테이블에 추가합니다.

14.) 모든 것이 올바르고 완전한지 확인합니다.나는 표를 보기 위해 GUI를 사용했다.

15.) 새 테이블의 이름을 원래 테이블 이름으로 변경합니다.

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';

마침내, 모든 것이 성공했어요!

일반적인 경우에서 이해했듯이 Identity 속성을 가진 Primary 키로 테이블을 만듭니다.
따라서 제약 조건 규칙이 열 구조를 검증하고 있기 때문에 기본 키 콘스트레인트와 연관된 열의 이름 변경 또는 삭제를 수행할 수 없습니다.
이를 위해서는 다음과 같은 방법으로 몇 가지 절차를 진행해야 합니다.
TableName = 'Employee' 및 ColumnName = 'Employee'로 가정합니다.아이디'

''EmployeeId_new' 표에 합니다. 'EmployeeId_new' 컬럼은 'EmployeeId_new'입니다.
1)ALTER 입니다.

  1. 테이블에서 '합니다.
    ALTER 테이블

  2. 기본 키 제약 조건 규칙이 적용 가능하고 열 구조를 검증 중이기 때문에 오류가 발생합니다.
    *## '메시지 5074, 레벨 16, 상태 1, 행 1 객체 [PK_dbo]Employee]는 colmn [EmployeeId]에 의존합니다.'###'

  3. '키하고 '직원' 하면 됩니다.
    ALTER TABLE [PK_dbo]★★★★★★★★★★★★★★★★★★」

  4. 할 수 .
    ALTER 테이블

  5. 에서 "되었습니다. 열 "을 "EmployeId"로 합니다. 「EmployeeId_new」는 「EmployeeId_new」로, 「EmployeeId」는 「EmployeeId」로 합니다.
    sp_rename ' 입니다.EmployeeId', 'EmployeeId_new', 'COLUMN'

  6. 하려면 "에 ""를 .
    ALTER TABLE [PK_dbo] 기본 키(] (EmployeeId)

8. 'EmployeeId' 표가 기존 기본 키 제약 조건과 함께 ID 규칙에 대해 수정되었습니다.

이렇게는 할 수 없습니다.다른 컬럼을 추가하고 원래 컬럼을 드롭하여 새 컬럼의 이름을 변경하거나 새 테이블을 만들고 데이터를 복사하여 새 테이블을 드롭한 후 새 테이블의 이름을 이전 테이블로 변경해야 합니다.

SSMS를 사용하여 디자이너에서 ID 속성을 ON으로 설정한 경우 SQL Server가 백그라운드에서 수행하는 작업을 보여 줍니다.따라서 [user]라는 이름의 테이블이 있는 경우 UserID와 ID를 생성하면 다음과 같이 됩니다.

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

시스템 테이블을 해킹하여 비트 값을 설정함으로써 달성할 수 있는 방법이 있다고 말했지만, 그것은 지원되지 않으며 나는 하지 않을 것이다.

혹시 Visual Studio 2017+를 사용하신다면

  1. Server Object Explorer에서 테이블을 마우스 오른쪽 버튼으로 클릭하고 "코드 표시"를 선택합니다.
  2. IDENTY라는 수식어를 열에 추가합니다.
  3. 갱신하다

이것으로 모든 것이 끝입니다.

설계상 기존 열의 ID 기능을 켜거나 끌 수 있는 간단한 방법은 없습니다.이를 위한 유일한 클린 방법은 새 열을 생성하여 ID 열을 만들거나 새 테이블을 만들고 데이터를 마이그레이션하는 것입니다.

SQL Server Management Studio를 사용하여 "id" 열의 ID 값을 삭제하면 새 임시 테이블이 생성되고 데이터가 임시 테이블로 이동되며 이전 테이블이 삭제되고 새 테이블 이름이 변경됩니다.

Management Studio를 사용하여 변경한 후 설계자를 마우스 오른쪽 버튼으로 클릭하고 "변경 스크립트 생성"을 선택합니다.

이것이 SQL Server가 백그라운드에서 수행하는 작업임을 알 수 있습니다.

안타깝게도 IDENTY 속성은 컬럼이 아닌 테이블에 속합니다.

보다 쉬운 방법은 GUI에서 실행하는 것이지만, 이것이 옵션이 아닌 경우 데이터를 복사하고 열을 삭제한 후 ID를 사용하여 다시 추가하고 데이터를 원래대로 되돌리는 방법을 사용할 수 있습니다.

자세한 내용은 여기를 참조하십시오.

을 ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★」PRIMARY KEY에서 사용할 수 있지만 .IDENTITYcolumn 두 가지 것과 같이 할 수 .column ( 2 지 、 른른른른 ）。

ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])

.PRIMARY KEY★★★★★★ 。

이 투고는 오래되어 의뢰자의 요구에 대해 추측하고 있습니다만, 이 추가 정보는 이 스레드를 접한 사용자에게 도움이 될 수 있다고 생각합니다.이러한 추가 정보는, 우선 새로운 컬럼으로 추가하지 않으면 기존의 컬럼을 프라이머리 키로 설정할 수 없다고 생각하기 때문입니다.

개체 탐색기에서 테이블 이름을 마우스 오른쪽 버튼으로 클릭합니다.몇 가지 선택지가 있을 거예요.'디자인'을 클릭합니다.이 테이블에 대한 새 탭이 열립니다.'열 속성'에서 ID 제약 조건을 추가할 수 있습니다.

열의 ID 속성을 수정하려면:

  • Server Explorer에서 변경할 ID 속성이 있는 테이블을 마우스 오른쪽 버튼으로 클릭하고 테이블 정의 열기를 클릭합니다.테이블 디자이너에서 테이블이 열립니다.
  • 변경할 열의 [Allow nulls]체크박스를 끄겠습니다
  • 열 속성 탭에서 ID 규격 속성을 확장합니다.
  • Is Identity 하위 속성의 그리드 셀을 클릭하고 드롭다운 목록에서 Yes를 선택합니다.
  • ID 시드 셀에 값을 입력합니다.이 값은 테이블의 첫 번째 행에 할당됩니다.디폴트로는 값 1이 할당됩니다.

그게 다야, 나한테는 효과가 있었어

tsql을 사용하여 기존 열을 ID 열로 변경할 수 없습니다.그러나 Enterprise Manager 설계 뷰를 통해 이를 수행할 수 있습니다.

또는 ID 열로 새 행을 만들고 이전 열을 삭제한 다음 새 열의 이름을 바꿀 수 있습니다.

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED

현재 상태로는 이 방법을 따르고 있습니다.스크립트를 통해 데이터를 삽입한 후 프라이머리 테이블에 아이덴티티를 부여하고 싶습니다.

아이덴티티를 추가하고 싶기 때문에 항상 1부터 원하는 레코드 카운트 종료까지입니다. 

--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID 
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)

--then add primary key to that column (exist option you can ignore)
IF  NOT EXISTS (SELECT * FROM sys.key_constraints  WHERE object_id = OBJECT_ID(N'[dbo].[PK_tblProductPriceList]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductPriceList]'))
    ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO

그러면 ID가 있는 동일한 기본 키 열이 생성됩니다.

다음 링크를 사용했습니다.https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/

기존 테이블에 기본 키 추가

기본적으로 4가지 논리적 단계가 있습니다.

  1. 새 ID 열을 만듭니다.이 새 열에 대해 ID 삽입을 설정합니다.

  2. 원본 열(Identity로 변환하려는 열)의 데이터를 이 새 열에 삽입합니다.

  3. 새 열의 ID 삽입을 해제합니다.

  4. 소스 열을 삭제하고 새 열의 이름을 소스 열의 이름으로 변경합니다.

복수의 서버간에 작업하는 등, 몇개의 복잡성이 더해지는 일이 있습니다.

순서(SSMS & T-sql 사용)에 대해서는, 다음의 문서를 참조해 주세요.이 단계는 T-SQL을 잘 이해하지 못하는 초보자를 위한 것입니다.

http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx

기본 키 = bigint로 ID가 설정되지 않은 모든 테이블에 대해 스크립트를 생성합니다. 그러면 생성된 스크립트 목록이 각 테이블과 함께 반환됩니다.

SET NOCOUNT ON;

declare @sql table(s varchar(max), id int identity)

DECLARE @table_name nvarchar(max),
        @table_schema nvarchar(max);

DECLARE vendor_cursor CURSOR FOR 
SELECT
  t.name, s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS (
    SELECT
    [c].[name]
    from sys.columns [c]
    join sys.types [y] on [y].system_type_id = [c].system_type_id
    where [c].[object_id] = [t].[object_id] and [y].name = 'bigint' and [c].[column_id] = 1
) and NOT EXISTS 
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
) and exists (
    select 1 from sys.indexes as [i] 
    inner join sys.index_columns as [ic]  ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
    where object_name([ic].[object_id]) = [t].[name]
)
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor 
INTO @table_name, @table_schema

WHILE @@FETCH_STATUS = 0
BEGIN

DELETE FROM @sql

declare @pkname varchar(100),
    @pkcol nvarchar(100)

SELECT  top 1
        @pkname = i.name,
        @pkcol = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM    sys.indexes AS [i]
INNER JOIN sys.index_columns AS [ic] ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) = @table_name

declare @q nvarchar(max) = 'SELECT  '+@pkcol+' FROM ['+@table_schema+'].['+@table_name+'] ORDER BY '+@pkcol+' DESC'

DECLARE @ident_seed nvarchar(max) -- Change this to the datatype that you are after
SET @q = REPLACE(@q, 'SELECT', 'SELECT TOP 1 @output = ')
EXEC sp_executeSql @q, N'@output bigint OUTPUT', @ident_seed OUTPUT

insert into  @sql(s) values ('BEGIN TRANSACTION')
insert into  @sql(s) values ('BEGIN TRY')

-- create statement
insert into  @sql(s) values ('create table ['+@table_schema+'].[' + @table_name + '_Temp] (')

-- column list
insert into @sql(s) 
select 
    '  ['+[c].[name]+'] ' +
    y.name + 

    (case when [y].[name] like '%varchar' then
    coalesce('('+(case when ([c].[max_length] < 0 or [c].[max_length] >= 1024) then 'max' else cast([c].max_length as varchar) end)+')','')
    else '' end)

     + ' ' +
    case when [c].name = @pkcol then 'IDENTITY(' +COALESCE(@ident_seed, '1')+',1)' else '' end + ' ' +
    ( case when c.is_nullable = 0 then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT ('+(
        REPLACE(
            REPLACE(
                LTrim(
                    RTrim(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        LTrim(
                                            RTrim(
                                                REPLACE(
                                                    REPLACE(
                                                        object_definition([c].default_object_id)
                                                    ,' ','~')
                                                ,')',' ')
                                            )
                                        )
                                    ,' ','*')
                                ,'~',' ')
                            ,' ','~')
                        ,'(',' ')
                    )
                )
            ,' ','*')
        ,'~',' ')
    ) +
    case when object_definition([c].default_object_id) like '%get%date%' then '()' else '' end
    +
    ')','') + ','
 from sys.columns c
 JOIN sys.types y ON y.system_type_id = c.system_type_id
  where OBJECT_NAME(c.[object_id]) = @table_name and [y].name != 'sysname'
 order by [c].column_id


 update @sql set s=left(s,len(s)-1) where id=@@identity

-- closing bracket
insert into @sql(s) values( ')' )

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] ON')

declare @cols nvarchar(max)
SELECT @cols = STUFF(
    (
        select ',['+c.name+']'
        from sys.columns c
        JOIN sys.types y ON y.system_type_id = c.system_type_id
        where c.[object_id] = OBJECT_ID(@table_name)
        and [y].name != 'sysname'
        and [y].name != 'timestamp'
        order by [c].column_id
        FOR XML PATH ('')
     )
    , 1, 1, '')

insert into @sql(s) values( 'IF EXISTS(SELECT * FROM ['+@table_schema+'].['+@table_name+'])')
insert into @sql(s) values( 'EXEC(''INSERT INTO ['+@table_schema+'].['+@table_name+'_Temp] ('+@cols+')')
insert into @sql(s) values( 'SELECT '+@cols+' FROM ['+@table_schema+'].['+@table_name+']'')')

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] OFF')


insert into @sql(s) values( 'DROP TABLE ['+@table_schema+'].['+@table_name+']')

insert into @sql(s) values( 'EXECUTE sp_rename N''['+@table_schema+'].['+@table_name+'_Temp]'', N'''+@table_name+''', ''OBJECT''')

if ( @pkname is not null ) begin
    insert into @sql(s) values('ALTER TABLE ['+@table_schema+'].['+@table_name+'] ADD CONSTRAINT ['+@pkname+'] PRIMARY KEY CLUSTERED (')
    insert into @sql(s)
        select '  ['+COLUMN_NAME+'] ASC,' from information_schema.key_column_usage
        where constraint_name = @pkname
        GROUP BY COLUMN_NAME, ordinal_position
        order by ordinal_position

    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  )')
end

insert into  @sql(s) values ('--Run your Statements')
insert into  @sql(s) values ('COMMIT TRANSACTION')
insert into  @sql(s) values ('END TRY')
insert into  @sql(s) values ('BEGIN CATCH')
insert into  @sql(s) values ('        ROLLBACK TRANSACTION')
insert into  @sql(s) values ('        DECLARE @Msg NVARCHAR(MAX)  ')
insert into  @sql(s) values ('        SELECT @Msg=ERROR_MESSAGE() ')
insert into  @sql(s) values ('        RAISERROR(''Error Occured: %s'', 20, 101,@msg) WITH LOG')
insert into  @sql(s) values ('END CATCH')

declare @fqry nvarchar(max)

-- result!
SELECT @fqry = (select char(10) + s from @sql order by id FOR XML PATH (''))


SELECT @table_name as [Table_Name], @fqry as [Generated_Query]
PRINT 'Table: '+@table_name
EXEC sp_executeSql @fqry

    FETCH NEXT FROM vendor_cursor 
    INTO @table_name, @table_schema
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

언급URL : https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column

반응형

'programing' 카테고리의 다른 글

낮은 버전의 SQL Server 데이터베이스 백업 복원  (0) 2023.04.08
여러 열에 고유한 제약 조건  (0) 2023.04.08
SQL Server 복원 오류 - 액세스가 거부되었습니다.  (0) 2023.04.08
OPTION(재컴파일)은 항상 고속입니다.그 이유는 무엇입니까?  (0) 2023.04.08
SQL 서버의 서로 다른 두 서버에서 데이터 선택  (0) 2023.04.08

'programing'의 다른글

  • 현재글기존 열에 ID 추가

관련글

댓글

티스토리툴바