I am writing a SQL job to replace some old legacy processing. Basically, it reads in a raw text file, does some manipulations to the data, then runs an update statement. Pretty simple. However, I ran into an issue with the BULK INSERT and I don't exactly understand the resolution, so I wanted to post it here.

The BULK INSERT statement I was writing was as follows:

-- Insert statements for procedure here
BULK INSERT [SomeTableName] FROM '\\FILE_PATH_HERE\test.txt'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '0x0a'--'\r\n'
);

When I ran original statement with the ROWTERMINATOR of '\r\n' no data was populated in my table. When I replaced '\r\n' with 'OxOa' my bulk insert worked.

The reason I am unsure why this worked, is that when I view file in Notepad++ with View->Symbols->Show all characters, I see CRLF at the end of each data line. I thought that '\r\n' was the equivalent of a carriage return line feed (CRLF), but it wasn't working. I thought 'Oxoa' only represented a '\n' so I am a little unsure why changing ROWTERMINATOR from '\r\n' to '0x0a' worked.

Any insight would be appreciated