Bulk Insert Using Stored Procedures: Troubleshooting

In an attempt to bulk insert data using a stored procedure, a developer encountered syntax errors. The goal was to replicate a working BULK INSERT query into a procedure. Here's an analysis of the problem and its resolution.

The issue arises from attempting to pass a file name as a variable to the BULK INSERT command within the stored procedure. Unfortunately, this is an unsupported operation by BULK INSERT.

Here's an example of an unsuccessful attempt using a variable:

DECLARE @filename VARCHAR(255)
SET @filename = 'e:\5-digit Commercial.csv'

BULK INSERT ZIPCodes
FROM @filename
WITH

Instead, the BULK INSERT command expects a hard-coded file path. To work around this limitation, consider constructing the BULK INSERT statement as a string with a fixed file name and executing it as dynamic SQL.

DECLARE @filepath NVARCHAR(500)
SET @filepath = N'e:\5-digit Commercial.csv'

DECLARE @bulkinsert NVARCHAR(2000)

SET @bulkinsert =
       N'BULK INSERT ZIPCodes FROM '''  
       @filepath  
       N''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'

EXEC sp_executesql @bulkinsert

By using dynamic SQL, the constructed statement can be executed, resolving the syntax errors encountered in the original stored procedure implementation.