Mr. Phil Games Blog

Bulk Insert Tip and Gotchas

A follow up tip to using the Bulk Insert trick with SQL Server.  You can edit the files using Excel and get around the broken comma escape problem by change the Windows setting for what a list delimiters is and using the pipe character, |, instead.  Source: Change Delimiter For Csv Export – Tip

Another gotcha I ran into is that the Bulk Insert doesn’t always use the id in the Identity columns and you can run into weird foegirn key problems.  You have to add the KEEPIDENTITY setting, for example:

BULK INSERT [dbo].[tablename]
FROM ‘C:tabeldatatablename.csv’
WITH
(
FIELDTERMINATOR =’|’,
ROWTERMINATOR = ‘n’,
FIRSTROW = 2,
KEEPIDENTITY
)

Role Base Custom Errors

Interesting way to use aspnetdb Roles to give developers detailed errors reports: Tip/Trick: Show Detailed ASP.NET Error Messages to Developers (and only to Developers)

Here’s an interesting way to populate a table with data from a script:

BULK INSERT [dbo].[tablename]
FROM ‘C:tabeldatatablename.csv’
WITH
(
FIELDTERMINATOR =’|',
ROWTERMINATOR = ‘n’,
FIRSTROW = 2
)

One gotcha, is the server running the sql instance has to have access permissions to that directory and file. Otherwise, you will get some errors like:

Cannot bulk load file ‘C:tabeldatatablename.csv’ could not be opened. Operating system error code 3(The system cannot find the path specified.)

or

Cannot bulk load file ‘C:tabeldatatablename.csv’ could not be opened. Operating system error code 5(Access is denied)

Here’s the helpful post that clued me in: BULK insert error with UNC path and windows Authentication