sql - 批量插入错误 : Incorrect syntax near '+'

Question

以下代码有什么问题？

BULK INSERT test
FROM 'myfile_'+ CONVERT(VARCHAR(20), GETDATE(), 112) + '.TXT'
    WITH
    (FIRSTROW = 2,
     FIELDTERMINATOR = '~',
     ROWTERMINATOR = '\n')

错误信息:

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '+'.

Answer 1

您不能将日期动态连接到批量插入语句中的文件名......

如果要执行此操作，则必须使用动态 Sql 构建语句，然后执行它:

DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 
'BULK INSERT test
FROM ''myfile_' + CONVERT(VARCHAR(20), GETDATE(), 112) + '.TXT''
    WITH
    (FIRSTROW = 2,
     FIELDTERMINATOR = ''~'',
     ROWTERMINATOR = ''\n'')'

EXEC(@Sql)