LOAD DATA INFILE in visual studio using SQL server ERROR: "The LOAD DATA SQL construct or statement is not supported"

Question

I have following problem: I have a .csv file with data (around 30mb). I like to load content of that file to my database more specific to my IPBlock table which look like this:

startIP: Int
endIP: Int
LocationID: Int

and content of a file looks like that:

   "16777216","16777471","17"
   "16777472","16778239","49"
   "16778240","16778495","14409"

I try to execute this query:

LOAD DATA LOCAL INFILE 'C:\Users\Molu\Desktop\GeoLiteCity_20131203\test.csv'
INTO TABLE IPBlock 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
(startIP , endIP, LocationID); 

and I got following errors

The LOAD DATA SQL construct or statement is not supported.

and

Error Source:".Net sqlClient Data Provider" Error message "Incorrect syntax near LOCAL"

I already try version with double"\" like: C:\\Users\\Molu and with and without "LOCAL" key-word (here only difference is that error message is: "Incorrect syntax near INFILE" )

Do you have any ideas ? Thanks in advance.

Yardarm

Answer 1

There is no LOAD DATA LOCAL INFILE in SQLServer; it's present in MySQL. You should be rather using bcp (Bulk Copy) utility to do the same.

See here on how to use the same

https://msdn.microsoft.com/en-us/library/ms188365.aspx

(OR)

Use Bulk insert like this way

BULK
INSERT IPBlock
FROM 'C:\Users\Molu\Desktop\GeoLiteCity_20131203\test.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Carpus