SQL Server Bulk Insert EDI file - row order jumbled

Question

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?

I assume that your target table has no keys or indices, correct? In any event, you shouldn't care what order the data are stored in the table. If you do care, you'll have to apply a clustered index. To the best of my knowledge, Bulk Insert doesn't guarantee to keep the records in "source order" — Brian, Aug 29 '18 at 20:33
Does your EDI file have a line number in it? Is it a header/detail type structure where it _has_ to appear in order? — Nick.Mc, Aug 30 '18 at 02:22
@Brian, You're correct, my target table does not have key or indices or line number, I was told it's not good to have a line_number column when do Bulk Insert. — Drex, Aug 30 '18 at 19:32
@Nick.McDermaid, my EDI file does not have a line number in it, it's like a header/detail type structure but I don't know if my BulkInsert recognize that... — Drex, Aug 30 '18 at 19:33
You need a line number column. Add an `IDENTITY` column to your table, then create a view that lists all columns _except_ the identity column, then insert into that view. Although I can't find any technical reassurance anywhere, this has always worked for me. — Nick.Mc, Aug 31 '18 at 11:27
@Nick.McDermaid, I understand your solution, however it won't work for me or maybe I am doing incorrectly, the bulk insert command won't be able to add content from file into the table with specific column, or in another word, ignore the identity column.https://stackoverflow.com/questions/14711814/ignore-certain-columns-when-using-bulk-insert — Drex, Aug 31 '18 at 15:19
It works fine for me. That post is about ignoring columns in the file. My solution is about ignoring columns in the target table. I will find a link or write it up — Nick.Mc, Aug 31 '18 at 22:26
As per Gordon Linoff's answer that you marked correct... it works fine. Just remember that you need to Order By the line number. Without Order By there is no guarantee — Nick.Mc, Sep 01 '18 at 08:01

Gordon Linoff · Accepted Answer · 2018-08-30T21:19:46.477

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.

Thank you very much! My bulk insert destination table would be like a staging table without a line_number column(only text column), are you suggesting I should add the "line number" column in my staging table? If so, how could my bulk insert command be? Will the 'bulk insert' insert by line number order automatically? — Drex, Aug 30 '18 at 19:41
@KevinDing, if you need your data in a certain order, you have to specify that when you query the data. If you simply type `select * from table` there is no telling how the data might be ordered. — Brian, Aug 30 '18 at 20:01

SQL Server Bulk Insert EDI file - row order jumbled

1 Answers1