Sql-server – using sp_executesql to run bulk insert

bulk-insertdynamic-sqlsql server

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)

PRODUCTID PRODUCTNAME PRODUCTGROUPCODE PRODUCTGROUPNAME INSTOCKFLAG FULLPRICE
10001 CA CALENDAR N 15

Best Answer

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.

Related Question