0

I'm trying to develop an app that can handle bulk inserts of files into a given table. The path of the files can vary so I wanted to develop a stored proc that I can call and pass in the path.

The following works well

BULK INSERT [dbo].[tblUTAClockExport]
FROM '\\warhawk\C$\test\clock_export_20180922.csv'
WITH (FIELDTERMINATOR = ',' );

... but I can't replace the path with a parameter. This fails:

declare @fname as varchar(200);
set @fname = '\\warhawk\C$\test\clock_export_20180922.csv';
BULK INSERT [dbo].[tblUTAClockExport]
FROM @fname
WITH (FIELDTERMINATOR = ',' );

Any suggestions for a work around?

bsivel
  • 2,821
  • 5
  • 25
  • 32
  • What error message do you get? Does that answer your question? Did you read the manual for `BULK INSERT`? – MatBailie Sep 21 '18 at 22:41
  • Use dynamic SQL – Alan Burstein Sep 22 '18 at 00:03
  • 1
    The documentation for [`BULK INSERT`](https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017) shows the argument as `FROM 'data_file' `. It must be quoted string, not a variable or expression. Dynamic SQL is an alternative. – HABO Sep 22 '18 at 03:27
  • 1
    Possible duplicate of [How to cast variables in T-SQL for bulk insert?](https://stackoverflow.com/questions/5019041/how-to-cast-variables-in-t-sql-for-bulk-insert) – HABO Sep 22 '18 at 03:27

1 Answers1

0

You can try to create string with your command and then execute it using EXEC:

declare @fname as varchar(200);
declare @command as varchar(max);

set @fname = '\\warhawk\C$\test\clock_export_20180922.csv';

set @command = 'BULK INSERT [dbo].[tblUTAClockExport]'
                +' FROM '+char(39)+@fname+char(39)
                +' WITH (FIELDTERMINATOR = '+char(39)+','+char(39)+' );'
EXEC(@command)
hamnis
  • 26
  • 6