EDITED after reading the MSDN forum link from the comment, very interesting.
Regardless of isolation level, two users cannot update a single page simultaneously, nor can any user read a partially updated page. Just imagine how SQL Server would deal with a page where the header says Col3 starts at byte 17. But it really starts at byte 25, because that part of the row hasn't been updated yet. There's no way a database could handle that.
But for rows larger than 8k, multiple pages are used, and that makes a half-updated column possible. Copied from the MSDN link (in case the link breaks), start this query in one window:
if object_id('TestTable') is not null
drop table TestTable
create table TestTable (txt nvarchar(max) not null)
go
insert into TestTable select replicate(convert(varchar(max),
char(65+abs(checksum(newid()))%26)),100000)
go 10
update TestTable set txt=replicate(convert(varchar(max),
char(65+abs(checksum(newid()))%26)),100000)
go 100000
This creates a table and then updates it with a string of 100.000x the same character. While the first query is running, start this query in another window:
while 1=1 begin
if exists (select * from TestTable (nolock) where left(Txt,1) <> right(Txt,1))
break
end
The second query stops when it reads a column that is half updated. That is, when the first character is different from the last. It will finish quickly, proving that it is possible to read half-updated columns. If you remove the nolock hint, the second query will never finish.
Surprising result! A half-updated XML column might break a (nolock) report, because the XML would be malformed.
As mentioned in the comments above, your time would be much better spent using SSIS for this task. Here's a recent video post that discusses handling multi-line record data files much like yours.
Since you're okay with spinning your wheels, let's try to find an answer anyway--if you have the data in a single row, you have somewhere to start. My next step would be to apply a split function. Here is an excellent article about the popular options for such a function. You will need to choose or adapt a function to handle (n)varchar(max) if your files exceed the standard limits. You'll just use your single row of data as the input parameter, and the carriage return as the delimiter.
Once your data is broken into rows, you should be able to liberally apply substring() to slice out your columns for each row type.
This probably won't perform well, but if you need to show a proof of concept to get access to SSIS, it might suffice.
Best Answer
The select below will add the spaces required for each field. The date format is fixed to 10 characters. The "|" sign is was added to show that the size of each coloum is fixed. Can be removed if not needed.