Importing Data into SQL Server 2005

i must say i was quite surprised, in a negative kind of way, by the SQL Server 2005 Express Edition feature set. I took the leap and migrated databases from MSDE2000Sp4. All is well in the Express Management Studio and it ticks over for about 90% of the tasks i need to perform… but then there’s importing data.

Ya think i could locate _anything_ to import a flat file [.CSV] into a table? And bcp doesn’t count.

Eish. The MS forums are a tirade with respect to this issue, so i’m not going to add my rant 🙂 What there is though, is a pretty nifty solution i found on the jackol’s den

SELECT *
INTO theImportTable
FROM
OPENROWSET(‘MSDASQL’,
‘Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\dev\documents;Extensions=CSV;’,
‘SELECT * FROM data.csv’)

And walah! 😀 Oh, and if you get an error like:

Ad hoc access to OLE DB provider ‘MSDASQL’ has been denied. You must access this provider through a linked server.

You can make changes according to MS Support.