Incorrect syntax near format in BULK INSERT?

sql sql-server sql-server-2016
Jake picture Jake · Feb 13, 2017 · Viewed 12.4k times · Source

I'm trying to figure out why the BULK INSERT command I'm using isn't recognizing the FORMAT and FIELDQUOTE options used in the command. 

BULK INSERT dbo.tblM2016_RAW_Current_Import_File
FROM '\\x\tms\SCADA.dat'
WITH
(
    FIRSTROW = 1,
    FORMAT = 'CSV',
    FIELDQUOTE = '"',
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'
)

For some reason, I'm getting the error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'FORMAT'.

Addition: FORMAT shows up in pink text in SSMS, FIELDQUOTE shows in black text, the others show in blue text. It seems the command isn't recognizing FORMAT and FIELDQUOTE as keywords for some reason.

Answer

Lasse V. Karlsen picture Lasse V. Karlsen · Feb 13, 2017

The FORMAT and FIELDQUOTE specifiers are unfortunately not yet available in production servers.

According to the documentation:

Input file format options
FORMAT = 'CSV'
Applies to: SQL Server vNext CTP 1.1.
Specifies a comma separated values file compliant to the RFC 4180 standard.

FIELDQUOTE = 'field_quote'
Applies to: SQL Server vNext CTP 1.1.

NOTE: This answer is current as of 13th of february 2017. At some point "SQL Server vNext" will cease to be CTP and become production server. The question, however, is in relation to SQL Server 2016 which thus lacks these features.

Related questions

Is there a LastIndexOf in SQL Server?

I am trying to parse out a value from a string that involves getting the last index of a string. Currently, I am doing a horrible hack that involves reversing a string: SELECT REVERSE(SUBSTRING(REVERSE(DB_NAME()), 1, CHARINDEX('_…

Read More
STRING_AGG is not a recognized built-in function name

I've downloaded and installed SQL Server 2016. When I attempted to use the STRING_AGG function I receive this error. Here is my code: SELECT STRING_AGG(cast(FieldNumber AS VARCHAR(100)), ',') FROM Fields I've installed SQL Server 2016 and SP1. …

Read More
Accessing JSON Array in SQL Server 2016 using JSON_VALUE

I am stuck while accessing array inside json using newly introduced JSON_VALUE function. Please consider following code - IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='JsonData') DROP TABLE JsonData; go CREATE TABLE JsonData(JsonData nvarchar(max)); …

Read More