I am trying to execute bulk insert statement using sp_executesql.
I received syntax error:

Msg 102, level 15, state 1
Incorrect syntax near '\t'.

my code block are based on this link

Please review my code and advise what went wrong and how can I correct it.

declare @filepath nvarchar(500);
set @filepath = N'c:\bcp\textfiles\product.txt';

declare @bulkinsert nvarchar(2000);
set @bulkinsert = 
N'bulk insert product from ''' + 
@filepath + 
N''' with (FIRSTROW = 2, FIELDTERMINATOR = "\t", rowterminator = "\n")';

exec sp_executesql @bulkinsert;

in product.txt file, the tab key is used as field delimiter below a sample from the text file
Note: first row is 6 columns (productname column has no value)

10001 CA CALENDAR N 15


This has nothing to do with sp_executesql. If you replace the exec call with PRINT @bulkinsert; - an easy way to perform local debugging - you will see this output:

bulk insert product from 'c:\bcp\textfiles\product.txt' 
  with (FIRSTROW = 2, FIELDTERMINATOR = "\t", rowterminator = "\n")

Put that in its own window, and you will see the exact same issue as the error you received is now highlighted by IntelliSense (note the red squiggly under the "\t" and the explanation in the tooltip if you hover over it):

enter image description here

The issue is you are using a double-quote (") as a string delimiter - this is not valid in T-SQL. You need to use single-quotes('') to delimit strings, but because the statement itself is embedded in another string, you need to double them up in order to escape them. So:

... @filepath + N''' with (FIRSTROW = 2, 
    FIELDTERMINATOR = ''\t'', rowterminator = ''\n'');';

In fact, in the answer you cite, those are single quotes, not double quotes - perhaps a transcription error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.