Why Doesn't BULK INSERT Accept Variable File Paths in Stored Procedures?

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:-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:-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.

The above is the detailed content of Why Doesn't BULK INSERT Accept Variable File Paths in Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!