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!
Thank!
