bulk insert expects raw data, and not formatted INSERT...statements.

if your file looked like this isntead:

1,Apr 27 2012 12:00AM

2,Apr 27 2012 12:00AM

bulk insert would insert into a two column table witht eh columns of type INT,datetime increadibly fast.

I've imported much wider files, for example a zip plus 4 database, where the text file was over a gig in size in under 15 minutes;

files with 100K rows of data in under a second are the norm.

here's a simple example, where i use bcp out to send a table to disk, and then bcp or BULK INSERT in the same data to a different table:

because I'm expecting CrLf in note and description fields, I like to use very odd delimiters that I'm sure will not exist in the same data.

--using a super special 4 char row delimiter to be sure it doesn't exist in the data

--flags explanation:

-- -c = character data

-- -t"[||]" = field terminator

-- -r"[~~]" = row terminator

-- -T' = Trusted connection

--out

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT EMAILTEMPLATESID, EMAILBODY FROM SandBox.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID" queryout c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

--in

EXECUTE master.dbo.xp_cmdshell 'bcp Production.dbo.EMAILTEMPLATES in c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

--in via bulk insert

BULK INSERT EMAILTEMPLATES FROM 'c:\Data\bcpExample.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = '[||]',

ROWTERMINATOR = '[~~]',

FIRSTROW = 1

)