ID가 있는 대량 삽입(자동 증분) 열

ID가 있는 대량 삽입(자동 증분) 열

CSV 파일에서 데이터베이스에 대량 데이터를 추가하려고 합니다.

에는 열이 .ID(PK) 자동 증가.

CREATE TABLE [dbo].[Employee](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) NULL,
 [Address] [varchar](50) NULL
) ON [PRIMARY]

다음 쿼리를 사용합니다.

BULK INSERT Employee  FROM 'path\tempFile.csv ' 
WITH (FIRSTROW = 2,KEEPIDENTITY,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');

.CSV 파일 -

Name,Address
name1,addr test 1
name2,addr test 2

그러나 다음과 같은 오류 메시지가 표시됩니다.

행 2, 열 1(id)에 대한 대량 로드 데이터 변환 오류(지정된 코드 페이지의 유형이 일치하지 않거나 잘못된 문자)입니다.

csv 파일에 id 열을 추가하고 비워 둡니다.

id,Name,Address
,name1,addr test 1
,name2,addr test 2

쿼리에서 KEEPIDITY 키워드 제거:

BULK INSERT Employee  FROM 'path\tempFile.csv ' 
WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');

ID 필드는 자동으로 증가합니다.

csv의 id 필드에 값을 할당하면 KEEPIDITY 키워드를 사용하지 않는 한 무시되고 자동 증분 대신 값이 사용됩니다.

실제 테이블에 직접 삽입물을 대량으로 넣지 마십시오.

나는 항상

1. 준비 테이블에 삽입dbo.Employee_Staging((으)로 안 함IDENTITY (에서) CSV 파일입니다.
2. 가져온 데이터를 편집/정리/조작할 수 있습니다.

3. 그런 다음 다음과 같은 T-SQL 문을 사용하여 데이터를 실제 테이블로 복사합니다.

   INSERT INTO dbo.Employee(Name, Address) 
      SELECT Name, Address
      FROM dbo.Employee_Staging
   

저도 비슷한 문제가 있었지만, ID의 순서가 소스 파일의 순서와 일치하는지 확인해야 했습니다.내 솔루션은 대량 삽입을 위해 뷰를 사용하는 것입니다.

테이블을 그대로 유지하고 이 VIEW를 만듭니다(ID 열을 제외한 모든 항목 선택).

CREATE VIEW [dbo].[VW_Employee]
AS
SELECT [Name], [Address]
FROM [dbo].[Employee];

그러면 BULK INSERT는 다음과 같이 표시됩니다.

BULK INSERT [dbo].[VW_Employee] FROM 'path\tempFile.csv ' 
WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');

형식 파일을 사용하여 대량 삽입을 수행해야 합니다.

   BULK INSERT Employee FROM 'path\tempFile.csv ' 
   WITH (FORMATFILE = 'path\tempFile.fmt');

여기서 형식 파일(tempFile.fmt)은 다음과 같습니다.

11.0
2
2 SQLCHAR 050 "\t" 2 이름 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 050 "\r\n" 3 주소 SQL_Latin1_General_CP1_CI_AS

자세한 내용은 여기에서 - http://msdn.microsoft.com/en-us/library/ms179250.aspx

제 해결책은 ID 필드를 테이블의 LAST 필드로 추가하여 대량 삽입이 이를 무시하고 자동 값을 얻는 것입니다.깔끔하고 단순한...

예를 들어, 임시 테이블에 삽입하는 경우:

CREATE TABLE #TempTable 
(field1 varchar(max), field2 varchar(max), ... 
ROW_ID int IDENTITY(1,1) NOT NULL)

로 고는 다음과 .ROW_ID필드는 항상 마지막 필드로 지정해야 합니다!

1. ID 열 + 기타 열을 사용하여 테이블을 만듭니다.
2. 뷰 위에 뷰를 작성하고 대량으로 삽입할 열만 표시합니다.
3. 보기의 BCP

저는 손실 시간을 만드는 바로 그 문제를 겪었기 때문에 저에게 효과가 있었던 제 연구 결과와 해결책을 공유하고자 영감을 받았습니다.

Excel 파일 사용

이것이 제가 채택한 접근법입니다.csv 파일을 사용하는 대신 아래와 같은 내용의 엑셀 파일(.xlsx)을 사용했습니다.

id  username   email                token website

    johndoe   johndoe@divostar.com        divostar.com
    bobstone  bobstone@divosays.com        divosays.com

ID 열에 값이 없습니다.

그런 다음 Microsoft SQL Server Management Studio를 사용하여 DB에 연결하고 데이터베이스를 마우스 오른쪽 단추로 클릭한 다음 데이터 가져오기(작업 아래 하위 메뉴)를 선택합니다.Microsoft Excel을 원본으로 선택합니다.원본 테이블 및 보기 선택 단계에 도달하면 매핑 편집을 클릭합니다.위해서id대상 아래의 열을 클릭하고 무시를 선택합니다.확인 안 함Enable Identity insert다른 데이터베이스에서 데이터를 가져오거나 원본 DB의 자동 증분 ID를 유지하려는 경우 ID를 유지하려는 경우를 제외합니다.끝까지 계속하면 끝입니다.데이터를 원활하게 가져올 수 있습니다.

CSV 파일 사용

csv 파일에서 데이터가 아래와 같은지 확인합니다.

id,username,email,token,website
,johndoe,johndoe@divostar.com,,divostar.com
,bobstone,bobstone@divosays.com,,divosays.com

아래 쿼리를 실행합니다.

BULK INSERT Metrics FROM 'D:\Data Management\Data\CSV2\Production Data 2004 - 2016.csv '
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

이 접근 방식의 문제는 CSV가 DB 서버 또는 DB가 액세스할 수 있는 일부 공유 폴더에 있어야 한다는 것입니다. 그렇지 않으면 "Cannot open file"과 같은 오류가 발생할 수 있습니다.운영 체제에서 오류 코드 21(장치가 준비되지 않음)을 반환했습니다."

원격 데이터베이스에 연결하는 경우 CSV를 해당 서버의 디렉토리에 업로드하고 대량 삽입 경로를 참조할 수 있습니다.

CSV 파일 및 마이크로소프트 SQL 서버 Management Studio 가져오기 옵션 사용

첫 번째 접근 방식처럼 가져오기 데이터를 시작합니다.소스에 대해 플랫 파일 소스를 선택하고 CSV 파일을 찾습니다.올바른 메뉴(일반, 열, 고급, 미리 보기)가 올바른지 확인합니다.열 메뉴(열 구분 기호)에서 오른쪽 구분 기호를 설정해야 합니다.위의 Excel 접근 방식과 마찬가지로 매핑 편집을 클릭합니다.대상 아래의 ID 열에 대해 해당 열을 클릭하고 무시를 선택합니다.

끝까지 계속하면 끝입니다.데이터를 원활하게 가져올 수 있습니다.

이것은 매우 오래된 답변입니다. 하지만 주어진 답변 중 어떤 것도 제시된 조건을 바꾸지 않고는 문제를 해결할 수 없습니다. 제가 할 수 없는 것입니다.

BULK INSERT의 OPENROWSET 변형을 사용하여 해결했습니다.이는 동일한 형식 파일을 사용하고 동일한 방식으로 작동하지만 SELECT 문으로 데이터 파일을 읽을 수 있습니다.

테이블 만들기:

CREATE TABLE target_table(
id bigint IDENTITY(1,1),
col1 varchar(256) NULL,
col2 varchar(256) NULL,
col3 varchar(256) NULL)

명령 창을 열고 실행:

bcp dbname.dbo.target_table format nul -c -x -f C:\format_file.xml -t; -T

이렇게 하면 표 모양에 따라 형식 파일이 만들어집니다.

이제 포맷 파일을 편집하고 FIELD ID="1" 및 COLUMN SOURCE="1"이 데이터 파일에 없으므로 전체 행을 제거합니다.
또한 데이터 파일에 필요한 경우 터미네이터를 조정합니다.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="256" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="256" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="256" COLLATION="Finnish_Swedish_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="2" NAME="col1" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="col2" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="col3" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

이제 선택 항목을 사용하여 테이블에 데이터 파일을 대량으로 로드할 수 있으므로, 이 경우 ID 열에 데이터를 삽입하지 않고 열을 완전히 제어할 수 있습니다.

INSERT INTO target_table (col1,col2, col3)
SELECT * FROM  openrowset(
bulk 'C:\data_file.txt',
formatfile='C:\format_file.xml') as t;

스테이징 테이블 대신 임시 테이블을 사용하는 경우 가져오기가 예상하는 대로 임시 테이블을 만든 다음 가져오기 후에 ID 열을 추가할 수도 있습니다.

따라서 SQL은 다음과 같은 작업을 수행합니다.

1. 온도 테이블이 있는 경우 드롭
2. 임시 테이블 만들기
3. 임시 테이블로 대량 가져오기
4. 변경 임시 테이블 ID 추가
5. < 당신이 데이터로 무엇을 하고 싶은지 >
6. 온도 테이블 삭제

아직 깨끗하지는 않지만, 다른 선택지는...안전을 위해 자물쇠도 채워야 할 수도 있습니다.

언급URL : https://stackoverflow.com/questions/10851065/bulk-insert-with-identity-auto-increment-column