Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

order of rows during bulk insert Expand / Collapse
Author
Message
Posted Monday, June 21, 2010 3:02 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 25, 2010 9:57 PM
Points: 40, Visits: 50
Thank you Lutz



Kind Regards,
Tim
The stone age did not end due to a lack of stones, nor will the information age end due to a lack of data.
Post #940650
Posted Tuesday, June 22, 2010 4:13 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:19 PM
Points: 178, Visits: 581
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.
Post #940883
Posted Tuesday, June 22, 2010 6:54 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 25, 2010 9:57 PM
Points: 40, 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
The stone age did not end due to a lack of stones, nor will the information age end due to a lack of data.
Post #940997
Posted Tuesday, June 22, 2010 7:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 2,917, Visits: 2,531
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
Post #941006
Posted Tuesday, June 22, 2010 6:33 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:19 PM
Points: 178, Visits: 581
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.
Post #941436
Posted Tuesday, June 22, 2010 6:51 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:19 PM
Points: 178, Visits: 581
... 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.
Post #941440
Posted Wednesday, June 23, 2010 8:00 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 25, 2010 9:57 PM
Points: 40, 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
The stone age did not end due to a lack of stones, nor will the information age end due to a lack of data.
Post #941763
Posted Wednesday, June 23, 2010 10:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 2,917, Visits: 2,531
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
Post #941932
Posted Wednesday, June 23, 2010 5:41 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:19 PM
Points: 178, Visits: 581
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.
Post #942148
Posted Thursday, June 24, 2010 1:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 449, Visits: 3,391
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.


David
Post #942667
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse