2

I try to perform bulk insert from csv file.

MY csv file having 7 columns but table contains 8 columns.

i can able to perform bulk insert with below query if my table having 8 columns only.

BULK INSERT Table_Name FROM 'E:\file\input.csv' WITH (ROWTERMINATOR = '0x0A',CODEPAGE = 'ACP',FIELDTERMINATOR = ',',KEEPNULLS, ROWS_PER_BATCH = 10000)

but my csv contains only 7 columns this leads below error..,

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

Can anyone suggest me way to resolve this without using FormatFile?

Mister X
  • 3,406
  • 3
  • 31
  • 72
  • 1
    I haven't found a way to do this, so would be interested to learn if it can be done. What I have done to get around this is to create a 'staging' table for the insert from the file, then do the rest once it is in the 'staging' table. – Keith Jun 13 '17 at 09:50

1 Answers1

1

Create a view with the 7 columns and insert into that view instead.

Example with fewer columns:

CREATE TABLE test_table(col1 int, col2 int, col3 int)
go

CREATE VIEW v_test_table
as 
SELECT col1, col2
FROM test_table

go

INSERT v_test_table
SELECT 1,2

go

SELECT * FROM test_table
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92