Bulk insert file path as parameter of stored procedure
I am trying to create a stored procedure to import from CSV
. Everything works if I have a hardcoded file path, but I want to take the file path as a parameter. When trying, SQL Sever Management Studio generates an error:
Incorrect syntax near '@filePath'.
(Actually, if I only put a blank line (like 'C:'+'/dir'
), it gives an error.)
This is a simplified version of my code:
Create procedure [importFile](@filePath varchar(Max))
AS
BEGIN
create table #Temp
(
row1 int,
row2 varchar(5),
row3 bit
)
BULK insert
#Temp
from @filePath
With(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
...
END
Any explanation?
+3
source to share
1 answer
Use dynamic SQL to insert a filename variable into a string with a bulk insert statement and use sp_executesql
it to execute it. You might want to add some error checking to check if the path is correct, etc.
CREATE PROCEDURE [importFile] (@filePath VARCHAR(MAX))
AS
BEGIN
CREATE TABLE #Temp
(
row1 int,
row2 varchar(5),
row3 bit
)
DECLARE @SQL NVARCHAR(MAX) = ''
SET @SQL = N'
BULK INSERT #Temp
FROM ''' + @filePath + '''
WITH (
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''
)'
-- ...
EXEC sp_executesql @SQL
END
-- to run it:
EXEC importFile 'd:\test.csv'
+3
source to share