Bulk Insert data to specific columns

Bulk insert will fail if we try to insert data to specific columns in a table, e.g. we have a Candidate table where we store the list of candidates and has an Identity column CandidateID

CREATE TABLE Candidate

(CandidateID INT IDENTITY(1,1) PRIMARY KEY,

 CandidateName VARCHAR(60),

 DOB DATETIME

)

Suppose our source file has only columns "CandidateName" and "DOB",  If we do BULK INSERT from source file to this candidate table as,

BULK INSERT Candidate

FROM 'F:\Candidates.csv'

WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

We will end up in an error,

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CandidateID).

An easy method to avoid this error is to create a view against the destination table with columns that we can provide as input and BULK INSERT to the created view.

CREATE VIEW vw_Candidate

(

 CandidateName VARCHAR(60),

 DOB DATETIME

)

BULK INSERT vw_Candidate

FROM 'F:\Candidates.csv'

WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

Now, the BULK INSERT is successful and our base table has the data.


Thank!