[sql-server] 테이블의 ID 열에 대한 명시 적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON 인 경우에만 지정할 수 있습니다.

이 쿼리를 수행하려고합니다.

INSERT INTO dbo.tbl_A_archive
  SELECT *
  FROM SERVER0031.DB.dbo.tbl_A

하지만 내가 달린 후에도

set identity_insert dbo.tbl_A_archive on

이 오류 메시지가 나타납니다

테이블 ‘dbo.tbl_A_archive’의 식별 컬럼에 대한 명시 적 값은 컬럼 목록이 사용되고 IDENTITY_INSERT가 ON 인 경우에만 지정할 수 있습니다.

tbl_A행과 너비가 큰 테이블입니다. 즉, 많은 열이 있습니다. 모든 열을 수동으로 입력하지 않아도됩니다. 이 기능을 작동 시키려면 어떻게해야합니까?

답변

요약

열 목록을 사용하지 않으면 SQL Server에서 ID 열에 명시 적 값을 삽입 할 수 없습니다. 따라서 다음과 같은 옵션이 있습니다.

열 목록 만들기 (수동 또는 도구 사용, 아래 참조)

또는

ID가 아닌tbl_A_archive 일반 열에 ID 열을 작성 하십시오. 테이블이 아카이브 테이블이고 항상 ID 열에 대한 명시 적 값을 지정하는 경우 ID 열이 필요한 이유는 무엇입니까? 대신 일반 int를 사용하십시오.

솔루션 1에 대한 세부 사항

대신에

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table
  SELECT *
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

당신은 쓸 필요가

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table (field1, field2, ...)
  SELECT field1, field2, ...
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

와 field1, field2, ...당신의 테이블에있는 모든 컬럼의 이름을 포함. 해당 열 목록을 자동 생성하려면 Dave의 답변 또는 Andomar의 답변을 살펴보십시오 .

솔루션 2에 대한 세부 사항

불행히도, ID int 열의 유형을 “identity int”열로 “변경”하는 것은 불가능합니다. 기본적으로 다음과 같은 옵션이 있습니다.

아카이브 테이블에 아직 데이터가없는 경우 열을 삭제하고 ID가없는 새 열을 추가하십시오.

또는

SQL Server Management Studio를 사용 하여 보관 테이블에있는 ID 열의 Identity Specification/ (Is Identity)속성을 로 설정하십시오 No. 씬 뒤에는 테이블을 다시 작성하고 기존 데이터를 복사하는 스크립트가 작성되므로 Tools/ Options/ Designers/ Table and Database Designers/ 설정을 해제해야합니다 Prevent saving changes that require table re-creation.

또는

이 답변에 설명 된 해결 방법 중 하나를 사용하십시오 . 테이블의 열에서 아이디 제거

답변

SET IDENTITY_INSERT tableA ON

INSERT 문에 대한 열리스트를 작성해야합니다.

INSERT Into tableA ([id], [c2], [c3], [c4], [c5] )
SELECT [id], [c2], [c3], [c4], [c5] FROM tableB

“INSERT Into tableA SELECT ……..”

SET IDENTITY_INSERT tableA OFF

답변

그냥 테이블을 마우스 오른쪽 버튼으로 클릭 – 당신은 SQL Server Management Studio를 사용하는 경우, 당신은 열 목록을 직접 입력 할 필요가 없습니다 개체 탐색기 를 선택 으로 스크립트 표 -> 에 SELECT -> 새 쿼리 편집기 창 .

그렇지 않은 경우 이와 유사한 쿼리가 시작점으로 도움이됩니다.

SELECT SUBSTRING(
    (SELECT ', ' + QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'tbl_A'
        ORDER BY ORDINAL_POSITION
        FOR XML path('')),
    3,
    200000);

답변

Heinzi의 답변에 동의하십시오. 첫 번째 두 번째 옵션의 경우 테이블에 쉼표로 구분 된 열 목록을 생성하는 쿼리가 있습니다.

select name + ', ' as [text()]
from sys.columns
where object_id = object_id('YourTable')
for xml path('')

큰 테이블의 경우 많은 타이핑 작업을 저장할 수 있습니다. 🙂

답변

“아카이브”테이블이 기본 테이블의 정확한 사본이되어야한다면 ID가 식별 열이라는 사실을 제거하는 것이 좋습니다. 그렇게하면 삽입 할 수 있습니다.

또는 다음 명령문으로 테이블에 대한 ID 삽입을 허용하고 허용하지 않을 수 있습니다.

SET IDENTITY_INSERT tbl_A_archive ON
--Your inserts here
SET IDENTITY_INSERT tbl_A_archive OFF

마지막으로 ID 열이 그대로 작동 해야하는 경우 언제든지 저장된 proc을 실행할 수 있습니다.

sp_columns tbl_A_archive

그러면 테이블의 모든 열이 반환되어 쿼리에 잘라 붙여 넣을 수 있습니다. (이것은 거의 항상 *를 사용하는 것보다 낫습니다)

답변

SQL 문의 경우 열 목록도 지정해야합니다. 예를 들어.

INSERT INTO tbl (idcol1,col2) VALUES ( value1,value2)

대신에

INSERT INTO tbl VALUES ( value1,value2)

답변

둘 다 작동하지만 # 1을 사용하여 여전히 오류가 발생하면 # 2로 이동하십시오.

SET IDENTITY_INSERT customers ON
GO
insert into dbo.tbl_A_archive(id, ...)
SELECT Id, ...
FROM SERVER0031.DB.dbo.tbl_A

SET IDENTITY_INSERT customers ON
GO
insert into dbo.tbl_A_archive(id, ...)
VALUES(@Id,....)