Jeff Moden (4/23/2009)

---

Actually.... let's just say the data from the original post was stored on the server in C:\Temp and the filename was called SomeFile.txt

Then, you can do a little SQL prestidigitation using Linked Servers...

--===== Create a text base linked server.

EXEC dbo.sp_AddLinkedServer TempTextServer,

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'C:\Temp', --LOOK!!! THIS IS THE DIRECTORY YOU WANT TO POINT AT!!!!

NULL,

'Text'

GO

--===== Set up the login (change the first null to a valid login name if you don't want SA)

EXEC dbo.sp_AddLinkedSrvLogin TempTextServer, FALSE, NULL, NULL

GO

--===== List the file names available in the new text based linked server.

-- Notice that the "dot" in file names has been replace by a # sign

EXEC dbo.sp_Tables_Ex TempTextServer

GO

--===== Query the file as if it were a table. Notice that the quotes and commas

-- are handled automatcially.

SELECT *

FROM TempTextServer...[SomeFile#txt]

Here's what the output of the original file looks like from the last SELECT above....

[font="Courier New"]id          company                      rep            employees

----------- ---------------------------- -------------- -----------

729216      INGRAM MICRO INC.            Stuart, Becky  523

729235      GREAT PLAINS ENERGY, INC.    Nelson, Beena  114

721177      GEORGE WESTON BAKERIES INC   Hogan, Meg     253

(3 row(s) affected)

[/font]

Of course, since it works like a table, you can do SELECT/INTO, INSERT/SELECT, or whatever you need to do.

There's no doubt about it... it WILL be slower than Bulk Insert... but it will work without having to write special parsing code.