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;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

select TerritoryID
      ,TotalSales
      ,TotalCost
INTO CSVImportTable
from 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   CsvImportTable
from   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)