BULK INSERT from variable Date Filename - ERROR

Question

I am trying to do an insert of a text file that contain datetime in filename.

declare @V_SQL varchar(100)
set @V_SQL = (select REPLACE(REPLACE(CONVERT(VARCHAR,getdate()-1,106), ' ',''), ',',''))
BULK INSERT [dbo].[test] FROM '"E:\test_"+ @V_SQL +".txt"'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '0x0a'
)
GO

When I run the above I get following message - BULK INSERT [dbo].[test] FROM '"E:\test_"+ @V_SQL +".txt"'

`FROM` only accepts a literal, not an expression (and your syntax isn't correct for that anyway). If you want a variable file name, you'll have to build the whole `BULK INSERT` statement dynamically (`REPLACE('BULK INSERT [dbo].[test] FROM $f WITH...', '$f', QUOTENAME('E:\test_' + @V_SQL + '.txt', ''''))`) and `EXEC` it. — Jeroen Mostert, Nov 13 '18 at 13:52
I've tried like:. but with delimiter is |. It seems not to work — Hung Nguyen, Nov 14 '18 at 01:36
Does this answer your question? [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) — wibeasley, Aug 07 '20 at 22:41

Aaron Bertrand · Accepted Answer · 2018-11-13T14:02:06.947

3

You can't put a variable or an expression there. You'll need to use dynamic SQL.

DECLARE @sql nvarchar(max) = N'BULK INSERT dbo.test FROM '''
  + 'c:\test_' 
  + REPLACE(CONVERT(char(11), DATEADD(DAY,-1,GETDATE()), 13),' ','') 
  + ''' WITH 
     (
       FIELDTERMINATOR = ''|'',
       ROWTERMINATOR = ''0x0a''
     );';

PRINT @sql;
--EXEC sys.sp_executesql @sql;

I strongly recommend:

not using shorthand for date operations (e.g. GETDATE()-1)
always declaring lengths for variable data types like varchar.

edited Nov 13 '18 at 14:02

answered Nov 13 '18 at 13:57

Aaron Bertrand

272,866
37
466
490

Yes. It's work. Thanks. If you have any basic and advance tutorials for Dynamic SQL. Pls send me. – Hung Nguyen Nov 14 '18 at 01:34
@HungNguyen I have a good example of [how to use dynamic SQL effectively here](https://blogs.sentryone.com/aaronbertrand/backtobasics-updated-kitchen-sink-example/), and a debugging tip [here](https://www.mssqltips.com/sqlservertip/3185/validate-the-contents-of-large-dynamic-sql-strings-in-sql-server/), but you should also be concerned about SQL injection (see [this](https://www.mssqltips.com/sqlservertip/3637/protecting-yourself-from-sql-injection-in-sql-server--part-1/) and [this](https://www.mssqltips.com/sqlservertip/3638/protecting-yourself-from-sql-injection-in-sql-server--part-2/)). – Aaron Bertrand Nov 14 '18 at 01:38
I really appreciate this. Thanks – Hung Nguyen Nov 14 '18 at 02:41

BULK INSERT from variable Date Filename - ERROR

1 Answers1