How do I load text or csv file data into SQL Server?
If you need to load data into SQL Server (e.g. from log files, csv files, chat transcripts etc), then chances are, you’re going to be making good friends with the BULK INSERT command.
The command to bulk insert comma-delimite data would be:
BULK INSERT OrdersBulk
FROM ‘c:\file.csv’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
If the csv file has a header row, try this:
BULK INSERT OrdersBulk
FROM ‘c:\file.csv’
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
Finally, you can also specify how many errors you want to allow before considering that the BULK INSERT failed.
BULK INSERT OrdersBulk
FROM ‘c:\file.csv’
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)