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


  Msg 102, level 15, state 1Incorrect 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
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):


  


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.