sql-servert-sqlbulkinsert

Concatenate Error with TSQL

What is wrong with the following code?

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

Thanks

Solution

  • You can't dynamically concatenate the date to the filename in the bulk insert statement...

    If you want to do this, you'll have to build up the statement using dynamic Sql then execute it:

    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)