TSQL Bulk Insert Syntax Error

Question

I run the following code, received a syntax error.

SQL Server 2012 Express (version 11) OS: Windows 7 home premium (x64 bit)

1. The server complains that I have a syntax error near ROWTERMINATOR='\n', I couldn't figure what is the cause for the issue.

2. I don't understand how to quotation mark works in bulk insert weblog from '''+@datapath+'access1.log''

My understanding is I should use double quotes to represent a single quote.

This is my code:

declare @datapath varchar(128);
set @datapath = 
    'C:\Users\admin121\sql-server-2014\sql2012_class3_20141028\WebLog\';

exec
('
  bulk insert weblog from '''+@datapath+'access1.log''
    with 
    (
      firstrow = 1,
      FIELDTERMINATOR ='  ',
      ROWTERMINATOR='\n', 
      MAXERRORS=99999999

    )
');

Answer 1

You need to escape the single quotes in the parameters, like:

exec
('
bulk insert weblog from '''+@datapath+'access1.log''
with 
(
  firstrow = 1,
  FIELDTERMINATOR =''  '',
  ROWTERMINATOR=''\n'', 
  MAXERRORS=99999999


)
');

Use the PRINT command to see what the command contains prior to EXECing it:

DECLARE @datapath VARCHAR(260);
SET @datapath = 'C:\some\path';
PRINT
('
bulk insert weblog from ''' + @datapath + '\access1.log''
with 
(
  firstrow = 1,
  FIELDTERMINATOR =''  '',
  ROWTERMINATOR=''\n'', 
  MAXERRORS=99999999
)
');

This provides the following output:

bulk insert weblog from 'C:\some\path\access1.log'
with 
(
  firstrow = 1,
  FIELDTERMINATOR ='  ',
  ROWTERMINATOR='\n', 
  MAXERRORS=99999999
)

The query pattern you are using is referred to as "dynamic SQL" and can be very problematic, especially for beginners, due to the problems with single quotes. Try running the following:

SELECT 'this is a string';
GO

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a ' + @somevar + ' test';
GO

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a '' + @somevar + '' test';
GO

At first glance, it might appear the third option should print something like:

this is a 'test' test

However, the output is:

Note the third result includes the name of the variable, instead of the content of the variable. In order to make the result correct, we need to write the statement like:

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a ''' + @somevar + ''' test';
GO

Notice the + @somevar + is now wrapped with three single quotes. This is equivalent to writing, and is indeed shorthand for, this:

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a ' + CHAR(39) + @somevar + CHAR(39) + ' test';
GO

Please refer to Erland Sommarskog's article on Dynamic SQL for the last and definitive word on how to use this powerful, yet sometimes confusing, feature.

Also, please be aware that dynamic SQL, although powerful and irreplaceable, can be a security nightmare through something known as SQL Injection. Please take a look at this technet page for details about how to identify and mitigate that. Also, you may want to search Duck Duck Go for "Little bobby tables", which takes you to this: