Bulk Insert Load Failed in SQL Server

Question

I'm trying to insert a .csv file into an existing table. The existing table has 3 columns and these are IDs (just being assigned in the table), Student Number, and Group Number.

In my .csv, I have the format below, but every time I insert it, I get an error

The bulk load failed. Unexpected NULL value in data file row, column 1. The destination column (ID) is defined as NOT Null.

So how do I ignore the first column?

This is the .CSV file:

   ID,StudentNumber,GroupNumber
   ,0000123456,3
   ,0000794613,3

The SQL that I'm using

    BULK INSERT [dbo].[Table_StudentNumber]
    FROM 'E:\CSV\sample.csv'
    WITH
    (
        FIRSTROW = 2,
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n'
    );

Look into using a format file with this. https://msdn.microsoft.com/en-us/library/ms178129.aspx — R. Richards, Dec 23 '16 at 22:57
Bulk insert into a temp table and then only select the columns you want into your actual table — S3S, Dec 24 '16 at 05:08

score 3 · Answer 1 · answered Dec 23 '16 at 23:24

3

Don't bulk insert your CSV into the real table. Insert it first in a table without the identity column, alter the data and then insert it into the real table.

answered Dec 23 '16 at 23:24

Nițu Alexandru

714
1
11
33

Hi. Are you referring to the ID column in the table? Pretty much the ID will be based on the next number available in the table. – cat4081 Dec 23 '16 at 23:27

Bulk Insert Load Failed in SQL Server

1 Answers1