I am using SQL Server Bulk Insert command to insert an EDI file content with terminator into my table, however, sometimes the order or rows got jumbled, for example:
BULK INSERT dbo.NDCArchitectBulkInsert
FROM '\\FTP\\TestFiles\\abc'
WITH (ROWTERMINATOR = '~')
File (abc) content:
Line 139 This is line 139~
Line 140 This is line 140~
...
Line 149 This is line 149~
Line 150 This is line 150~
Line 151 This is line 151~
...
Line 160 This is line 160~
Line 161 This is line 161~
Line 162 This is line 162~
After bulk insert, my table row would be like:
Line 139 This is line 139~
Line 140 This is line 140~
...
Line 149 This is line 149~
Line 160 This is line 160~
Line 161 This is line 161~
Line 150 This is line 150~
Line 151 This is line 151~
Line 162 This is line 162~
Line 160 and 161 somehow got inserted/jumbled in between Line 149 and Line 150, and this seems happen on random line number, I have tested if it's data issue but it's not... does anyone have any related experience and solution around that?
They did not get inserted "jumbled". How are you retrieving? Ahh, that's the issue. Result sets (like tables) are inherently unordered. So, if you are looking at the data using just a select, then you might see things out of order.
The simplest solution is if the first column is a line number or something. Then you can do:
select abc.*
from abc
order by abc.linenumber;
And everything will look right.
EDIT:
If you need to add a line number, you can load into a view. Something like this:
CREATE TABLE NDCArchitectBulkInsert (
NDCArchitectBulkInsertId int identity(1, 1) primary key,
. . . -- rest of the columns
);
CREATE VIEW v_NDCArchitectBulkInsert as
SELECT . . . -- rest of columns but not id
FROM NDCArchitectBulkInsert;
BULK INSERT v_NDCArchitectBulkInsert
FROM '\\FTP\\TestFiles\\abc'
WITH (ROWTERMINATOR = '~');
I'm not 100% sure that the behavior is guaranteed, but I think in practice this will update the identity in insertion order.