Import CSV file - dynamic path

Question

I am trying to import a csv file dynamically.

This works:

BULK
INSERT #CSV
FROM 'C:\Users\MyName\Desktop\Players.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

I would like to change it to be with a @path parameter like that:

DECLARE @path NVARCHAR(4000) = 'C:\Users\MyName\Desktop\Players.csv';

BULK
INSERT #CSV
FROM @path
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

But this does not compiles.

How could I do that?

Thanks in advance.

Answer 1

You would need to use Dynamic SQL to pass a dynamic file path to the Bulk Insert.

DECLARE @FileName NVARCHAR(4000);

SET @FileName = '/path/to.csv';

DECLARE @sql NVARCHAR(4000) = 'BULK INSERT #CSV 
        FROM ''' + @FileName + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )';

EXEC(@sql);