How to Import a CSV in SQL Server

Importing a CSV file into SQL Server can be done within PopSQL by using either BULK INSERT or OPENROWSET(BULK...) command.

The BULK INSERT command is used if you want to import the file as it is, without changing the structure of the file or having the need to filter data from a file. You need to create a table within the database that has the same structure as the CSV file you want to import prior to running the BULK INSERT command.

BULK INSERT sessions
from 'C:\TempDocs\Sessions.csv'
with (firstrow = 2,
      fieldterminator = ',',
      rowterminator='\n',
      batchsize=10000,
      maxerrors=10);

If you want to filter out data from a source file and import only particular table columns, use the OPENROWSET(BULK...) command. You don't need to create a database table previously: the command will create it automatically once you run the query. However, you need to define a format file that will hold a table definition of a table to be created automatically.

select id, start_date
into sessions
from OPENROWSET(BULK 'C:\TempDocs\Sessions.csv',
                formatfile='C:\TempDocs\Sessions.fmt',
                firstrow=2) as sessions_insert;
database icon
Better SQL for the people
Get more done with PopSQL and SQL Server