SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


order of rows during bulk insert


order of rows during bulk insert

Author
Message
TIM.OGRADY
TIM.OGRADY
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 50
Thank you Lutz



Kind Regards,
Tim
CoolThe stone age did not end due to a lack of stones, nor will the information age end due to a lack of data.Cool
nick.mcdermaid
nick.mcdermaid
SSC Eights!
SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)

Group: General Forum Members
Points: 838 Visits: 782
Yes SSIS will process it in the order that it finds it (or in the order that the source driver returns it). But you need to remember that there is no such thing as 'order' in SQL Server. The only way to gurantee the order that records are returned in is to use an order by query.

Following on from this you must have a seperate field with the line number in order to guarantee that data will be returned in a certain order.

I'm not familiar with the 'last' function in MS Access, but all equivalent functions in SQL Server require some sort of key to order by.
TIM.OGRADY
TIM.OGRADY
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 50
Thanks Nick,

So would importing using SSIS into a table with an identity column work to ensure the original sequence? Thus a query like the following would match the source:

Select Id_Column, *
From <table>
Order by Id_Column Asc



Kind Regards,
Tim
CoolThe stone age did not end due to a lack of stones, nor will the information age end due to a lack of data.Cool
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4778 Visits: 2907
If you bulk insert the file into a table without a primary key on it, the records will be inserted in the order that they are in the file. After they are loaded, modify the table to add an identity field and set that as your primary key. This will ensure that the records are maintained in the order they were loaded.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
nick.mcdermaid
nick.mcdermaid
SSC Eights!
SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)

Group: General Forum Members
Points: 838 Visits: 782
That would ensure that data flows through SSIS in the order that you have specified, yes.

But say it loads it into a target table called MyTable

If you then select it out of the database like this:

SELECT * FROM MyTable

Then there is NO guarantee whatsoever of the order that it will be returned in.


If you then select it out of the database like this:

SELECT * FROM MyTable
ORDER BY ID_Column

Then it will come out in the order that you require.


Even if you create a clustered index on ID_Column in your table there is still no guarantee that data will come out in a specific order if you do not specify an order by.


I understand that your data has header, detail, footer rows so the 'order' is important. Are you relying on this order to perform processing in SSIS or are you relying on this to perform processing in the database?

Basically you must save some kind of 'order' key along with the data in your table. Thats the only way to guarantee that you can return it in a certain order. The order in which the data is loaded has nothing to do with it.
nick.mcdermaid
nick.mcdermaid
SSC Eights!
SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)

Group: General Forum Members
Points: 838 Visits: 782
... not to argue with Steve Jimmo's point. In answer to 'how do I make sure that the data in the table is ordered as it is loaded', Steve is 100% right. But to take it a step further, ordering the data in the table does not guarantee that it will always be returned in that order.

So if you are relying on processing the data in a given order at a later date, you must have a 'order by' key. Which incidentally, Steves suggestion also solves nicely.
TIM.OGRADY
TIM.OGRADY
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 50
Thanks Steve and Nick,

Yes, I understand to retrieve the data I am ordering it by the Id_Column. My concern was that because the database retrieves the data in what the database deems most efficient (unless order is specified) I thought it might be loaded into the table that way too.

Again, Thank you all! It's been very helpful.

Tim



Kind Regards,
Tim
CoolThe stone age did not end due to a lack of stones, nor will the information age end due to a lack of data.Cool
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4778 Visits: 2907
Nick,

What argument? :-)

Tim (and Nick)

The data is stored in the order that it is loaded. I do not think that your issue is actually how it is stored.

When you issue a select * from tablename on a table without a PK or clustered index, the data should be returned in the order that it appears in the table using the collation order. If there is a clustered index on the table it will return the data in the order of the PK. Lastly in the order of the order by clause when you use it.

Now, the next issue will be to match the parent records to the child records. How are you planning to do that?

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
nick.mcdermaid
nick.mcdermaid
SSC Eights!
SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)

Group: General Forum Members
Points: 838 Visits: 782
In the end, the order the data is loaded into a table is usually irrelevant unless a clustered index already exists on the table and you want to take advantage of the fact that the source data is already in the same order as the clustered index... maybe thats what you're getting at? Lots more info here:

http://msdn.microsoft.com/en-us/library/aa196737(v=SQL.80).aspx

The only thing I need to add (and this may be also be irrelevant to the original question) is that you should never rely on rows returning in a specific order without an order by. The rows are returned in the order that the query plan decides to return them, and for now they do exactly as Steve says but who knows what magnificent enhancements will be done to SQL Server in future that might cause the query plan to be rearranged in some way. Adding an order by guarantees the last step of the query plan orders the data.
sqlvogel
sqlvogel
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2078 Visits: 3706
sjimmo (6/23/2010)
Nick,
When you issue a select * from tablename on a table without a PK or clustered index, the data should be returned in the order that it appears in the table using the collation order. If there is a clustered index on the table it will return the data in the order of the PK.


Neither of those statements is true (not even sure what the "order that it appears in the table using the collation order" actually means). If you issue a SELECT statement without ORDER BY then the ordering of rows returned is undefined and could even be different when you execute the same SELECT again even if the data has not changed. You cannot guarantee to return rows in any specific order unless you use ORDER BY. This is true even when the table has a clustered index - a fact that's often very easy to prove for yourself if you try it out.

There is no guarantee that an IDENTITY column will be assigned in a particular order for a bulk insert. If you create the IDENTITY column after the insert then there is no guarantee that the order of the IDENTITY values will match the order rows were inserted. In fact the row insertion order isn't preserved or recorded anywhere in the table or index structure.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search