How to create and populate a table in a single step as part of a CSV import operation?

Referencing SQLServerPedia, I think this will work:

sp_configure 'show advanced options', 1;RECONFIGURE;GOsp_configure 'Ad Hoc Distributed Queries', 1;RECONFIGURE;GOselect TerritoryID      ,TotalSales      ,TotalCostINTO CSVImportTablefrom openrowset('MSDASQL'               ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'               ,'select * from C:\csvtest.CSV')

Annoying, I don't have the rep points yet to just comment, so I'll add an answer based on TyT's (that handle looks terrible in possessive, btw ...)

The worker code needed a double "\" instead of a single for me to avoid a "file not found" error. And you don't have to specify the fields; they will be inferred from the first row of the file:

select *into   CsvImportTablefrom   openrowset(           'MSDASQL',           'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',           'select * from C:\\csvtestfile.csv')

I had no problems with the Access driver.

UPDATE: If you have trouble with the types being inferred incorrectly, insert a few rows at the top of the file with data of the type you want in the table so you get, say text -> VARCHAR instead of text-> INT and then delete those rows after the import.

As the final icing, add a PK to the table so you can manipulate the data - delete the dummy rows, etc:

alter table CsvImportTable add Id int identity(1, 1)

Updated answer if you're using SQL Server Management Studio 17.

Right click on Database -> Tasks -> Import Flat File...

It will automatically infer the first row of the data as the column names. It should automatically pick up the terminators. You will get the option to set primary keys, allowing nulls, and specify data types for the columns as well.