How to Use Variables with BULK INSERT in a SQL Stored Procedure?

Bulk Insert Data using Stored Procedure

You have created a query that successfully performs a bulk insert task. However, you now wish to encapsulate this query within a stored procedure to enhance reusability. Unfortunately, you have encountered syntax errors while attempting to do so.

The crux of the issue lies in the inability of the BULK INSERT command to accept a file name as a variable. While your code appears syntactically correct, the following code snippet will fail:

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

BULK INSERT ZIPCodes 
FROM @filename
WITH 

Therefore, it is not feasible to pass the file name as a variable to the BULK INSERT statement. Instead, you can dynamically build the BULK INSERT statement as a string and execute it using dynamic SQL. Here's an example:

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

This code assigns the file path to the variable @filepath. It then constructs the BULK INSERT statement as a string and stores it in the variable @bulkinsert. Finally, the dynamic SQL procedure sp_executesql is employed to execute the constructed statement. This approach allows you to parameterize the file path and execute the BULK INSERT statement within a stored procedure effectively.

The above is the detailed content of How to Use Variables with BULK INSERT in a SQL Stored Procedure?. For more information, please follow other related articles on the PHP Chinese website!