Importing Data into SQL Server 2005
25.04.07Filed Under: sql, technology
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.
Leave a Reply
Subscribe to comments feed (this is global, not just for this entry)
