bulk insert ordering of rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

bulk insert ordering of rows

Author

Topic

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-21 : 09:10:20

Can anyone create an example of a bulk insert or bcp where the rows aren't loaded in the order of the text file.
e.g. if you have an identity column on the able the identity values don't follow the order of the file rows.

I'm having a discussion with someone about it.
This has happened to me in the past but of course I can't recreate it.

I think it might be due to the fragmentation of the file on disk on connected to the number of processors.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-21 : 10:35:14

Confirmation that it has happened to someone else would be good too.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 11:24:46

I'm struggling a bit with how this could happen (no use in your thinking process I appreciate).

I can't think of any reason why BCP would read the file out-of-order (give the next-cluster-but-one to CPU number 2, for example)

I suppose the "file reader" could give the next Row to CPU2 and the row after that to CPU3, and CPU3 could beat CPU2 to getting the IDENTITY. But then out-of-order BCP Identity assignment would be common, wouldn't it?

What about some staging in TEMPDB - could that alter the order? (What happens if you have a Clustered PK and don't use the "sorted-by-PK" hint? Does it all go into TEMPDB, get sorted, then inserted - but IDENTITY must be assigned at the Insert to TEMPDB, rather than the insert into the final table, mustn't it?

I read something recently to the effect that rows are not guaranteed to be inserted in order, but that the Identity was guaranteed to be assigned in the "right" order. I think it was to do with:

INSERT INTO MyTable
SELECT Col1, Col2
FROM MyOtherTable
ORDER BY Col3

where MyTable has a third column which is IDENTITY. Might that evoke what you are thinking of?

Just thinking out loud, don't suppose its any help though!

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-21 : 11:41:38

Certainly if you insert into a table with an identity an order by isn't respected and I've had cases of the same with a bulk insert (or bcp).
I've got round the insert by inserting into a temp table, adding a clustered index then inserting into the final table - which seems to work.
Also two levels (but not one) of derived tables used to help - but v2005 is better at spotting that sort of thing so it might not any more.
INSERT INTO MyTable
select Col1, Col2 from
(select top 100 percent * from
(SELECT top 100 percent Col1, Col2, Col3
FROM MyOtherTable
ORDER BY Col3
) t
order by col3) t2
) t3

Really, really, I'm sure it happened, it's not my imagination and I wasn't under the influence.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 11:45:39

"I've got round the insert by inserting into a temp table"

Yup, you're right, I meant INSERT INTO #_or_@MyTable

Dunno about the BCP thing though, sorry.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources