ID (자동 증가) 열이있는 BULK INSERT

ID (자동 증가) 열이있는 BULK INSERT

---

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)에 대한 대량로드 데이터 변환 오류 (지정된 코드 페이지의 유형 불일치 또는 유효하지 않은 문자).

---

실제 테이블에 직접 대량 삽입하지 마십시오 .

나는 항상

1. CSV 파일에서 열 없이 스테이징 테이블에 삽입dbo.Employee_StagingIDENTITY
2. 가져온 데이터를 편집 / 정리 / 조작 할 수 있습니다.

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

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

---

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

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

쿼리에서 KEEPIDENTITY 키워드 제거 :

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

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

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

---

비슷한 문제가 있었지만 ID의 순서가 소스 파일의 순서와 일치하는지 확인해야했습니다. 내 솔루션은 BULK INSERT에 대한 VIEW를 사용하고 있습니다.

테이블을 그대로 유지하고이 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
1 SQLCHAR 0 50 "\ t"2 이름 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "\ 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. 엑셀 파일 사용

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

id  username   email                token website

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

id 열에는 값이 없습니다.

Next, connect to your DB using Microsoft SQL Server Management Studio and right click on your database and select import data (submenu under task). Select Microsoft Excel as source. When you arrive at the stage called "Select Source Tables and Views", click edit mappings. For id column under destination, click on it and select ignore . Don't check Enable Identity insert unless you want to mantain ids incases where you are importing data from another database and would like to maintain the auto increment id of the source db. Proceed to finish and that's it. Your data will be imported smoothly.

2. Using CSV file

In your csv file, make sure your data is like below.

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

Run the query below:

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

The problem with this approach is that the CSV should be in the DB server or some shared folder that the DB can have access to otherwise you may get error like "Cannot opened file. The operating system returned error code 21 (The device is not ready)".

If you are connecting to a remote database, then you can upload your CSV to a directory on that server and reference the path in bulk insert.

3. Using CSV file and Microsoft SQL Server Management Studio import option

Launch your import data like in the first approach. For source, select Flat file Source and browse for your CSV file. Make sure the right menu (General, Columns, Advanced, Preview) are ok. Make sure to set the right delimiter under columns menu (Column delimiter). Just like in the excel approach above, click edit mappings. For id column under destination, click on it and select ignore .

Proceed to finish and that's it. Your data will be imported smoothly.

---

Another option, if you're using temporary tables instead of staging tables, could be to create the temporary table as your import expects, then add the identity column after the import.

So your sql does something like this:

1. If temp table exists, drop
2. Create temp table
3. Bulk Import to temp table
4. Alter temp table add identity
5. < whatever you want to do with the data >
6. Drop temp table

Still not very clean, but it's another option... might have to get locks to be safe, too.

---

1. Create a table with Identity column + other columns;
2. Create a view over it and expose only the columns you will bulk insert;
3. BCP in the view

---

This is a very old post to answer, but none of the answers given solves the problem without changing the posed conditions, which I can't do.

I solved it by using the OPENROWSET variant of BULK INSERT. This uses the same format file and works in the same way, but it allows the data file be read with a SELECT statement.

Create your table:

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

Open a command window an run:

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

This creates the format file based on how the table looks.

Now edit the format file and remove the entire rows where FIELD ID="1" and COLUMN SOURCE="1", since this does not exist in our data file.
Also adjust terminators as may be needed for your data file:

<?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>

Now we can bulk load the data file into our table with a select, thus having full controll over the columns, in this case by not inserting data into the identity column:

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

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