이 쿼리를 수행하려고합니다.
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로 이동하십시오.
1)
SET IDENTITY_INSERT customers ON
GO
insert into dbo.tbl_A_archive(id, ...)
SELECT Id, ...
FROM SERVER0031.DB.dbo.tbl_A
2)
SET IDENTITY_INSERT customers ON
GO
insert into dbo.tbl_A_archive(id, ...)
VALUES(@Id,....)