I run the following code, received a syntax error.
SQL Server 2012 Express (version 11)
OS: Windows 7 home premium (x64 bit)
-
The server complains that I have a syntax error near
ROWTERMINATOR='\n'
,
I couldn't figure what is the cause for the issue. -
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
)
');
Best Answer
You need to escape the single quotes in the parameters, like:
Use the
PRINT
command to see what the command contains prior toEXECing
it:This provides the following output:
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:
At first glance, it might appear the third option should print something like:
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:
Notice the
+ @somevar +
is now wrapped with three single quotes. This is equivalent to writing, and is indeed shorthand for, this: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: