Insert data from CSV to SQL server, insert problem

Asked Jan 09 '20 at 09:29

Active Jan 09 '20 at 11:09

Viewed 76 times

I have one problem with bulk insert. I have about 7 million records in the csv file, when I make the following query, just half of the total records are entered in the table. The Query I use:

BULK INSERT *Table_Name* FROM 'file path'
    WITH
        (
        FIRSTROW = 1, -- csv dont have header
        FIELDTERMINATOR = '0x0a',
        ROWTERMINATOR = '\n',
        ERRORFILE = '*error file path*',
        TABLOCK
        )

Table Creation query:

CREATE TABLE *Table_Name*
(
    ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    *Column_Name* varchar(50)
);

Where I make a mistake? How to load all records from a csv file (all 7 million)?

asked Jan 09 '20 at 09:29

Bain-27

1

why `FIELDTERMINATOR = '0x0a'` ? This is `LINE FEED` character – Squirrel Jan 09 '20 at 09:38
As Squirrel said, if it's a CSV file why isn't `FIELDTERMINATOR = ','` or something? – AlwaysLearning Jan 09 '20 at 09:44
Becouse with ',' I got error - The bulk load failed. The column is too long in the data file for row 1, column 1. – Bain-27 Jan 09 '20 at 09:45
https://stackoverflow.com/questions/14365381/column-is-too-long-error-with-bulk-insert – Bain-27 Jan 09 '20 at 09:47
You can add BATCHSIZE parameter two your BULK INSERT script so that you decrease inserted row number one time. – Esat Erkec Jan 09 '20 at 11:30
@esat the problem is the same, I try different BATCHSIZE values – Bain-27 Jan 09 '20 at 11:40

Insert data from CSV to SQL server, insert problem

0 Answers0