July 26, 2011 at 12:28 am
Howdy guys,
I've run into a bit of a weird problem so I hope you folks can help. :blink:
I've been tasked with bringing data related to rail safety incidents from an old Access database that has started to corrupt into a newer SQL Server database (I didn't build either, I've just been brought in to help) but I have to do so manually as despite the purpose of both databases being the same their design and structures are dramatically different.
There are three tables in particular that contain the necessary data that needs to be brought across. One is the incident details, the second is how the incident is classified and the third is people and property details. Now I've got those three tables set up and good to go in a new Access database to keep them seperate and to aid in updating and correcting the informatio and they seem to import into SQL Server without any errors however the incident details table is jumbled up. By that I mean the rows have been moved around, like what should be row 2 is row 13 and row 4000 is now row 7000 and so on. The other two tables are perfectly fine but this table refuses to import and stay in order.
What would cause this and how do I fix it?
Thanks in advance,
Chazz
July 26, 2011 at 1:45 am
Tables don't have an order. No such concept. Tables are an unordered set of rows. If you want an order to the returned rows, you must use an Order By statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2011 at 3:57 am
I'm not really sure I follow. :blink:
If I have a table in Access that is full of data and I've ordered the rows by date then when I import the tables into SQL Server shouldn't each row remain where it was i.e. Row 1 in the table in Access is still Row 1 in the table in SQL.
If not then how is it that two of my tables (each with 5000+ rows of data) are exactly as they are in Access.
July 26, 2011 at 4:23 am
Tables by definition do not have a order for the rows in them, they are an unordered set of rows. There's no such thing as 'row 1' in a table. If you want the rows ordered in a particular way when you query them, you MUST specify an Order By clause.
There's tonnes written on this for SQL Server
As for how the other two tables appear to have the same order - chance. Without an Order By, the rows appear in whatever order the last operation in the query execution left it.
Regardless, you can never depend on rows appearing in a particular order unless you specify an Order By when querying the data.
From one of the people who writes the query optimiser: http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2011 at 5:45 am
I'm definitely no expert with SQL Server but that just doesn't seem right . Every time (five times thus far) I do the import (fixing things I've missed and such I import all three again from Access to make sure everything goes smoothly) the same two tables are perfect and the one other table is always jumbled and always jumbled the same way.
If it's of any significance, I'm using the SQL import/export wizard to bring the tables over, not queries.
July 26, 2011 at 5:54 am
Chazz88 (7/26/2011)
I'm definitely no expert with SQL Server but that just doesn't seem right . Every time (five times thus far) I do the import (fixing things I've missed and such I import all three again from Access to make sure everything goes smoothly) the same two tables are perfect and the one other table is always jumbled and always jumbled the same way.If it's of any significance, I'm using the SQL import/export wizard to bring the tables over, not queries.
Let me compound what and SQL MVP and Microsoft employee just told you.
There's nothing requiring SQL Server to display the rows in any particular order UNLESS you use order by in ANY AND ALL select queries.
ACCESS <> SQL SERVER. You have to assume different behavior.
Assuming you really have a problem, you got about fixing it by using order bys in the new application (or back in access if it's been converted to an ADP)
July 26, 2011 at 6:05 am
Chazz88 (7/26/2011)
...By that I mean the rows have been moved around, like what should be row 2 is row 13 and row 4000 is now row 7000 and so on. The other two tables are perfectly fine but this table refuses to import and stay in order.
Are the rows explicitly numbered in Access (using AutoNumber, I think)? If so, are the destination tables in SQL Server also endowed with a numbering column (called IDENTITY in SQL Server)? Is it this numbering that you wish to preserve?
The wizard you are using uses a SQL Server component called SSIS (SQL Server Integration Services) to load the data. There is an option (hidden away on one of the Options buttons) to enable identity insert - this would preserve the row numbering (assuming the source and destination columns are mapped correctly).
If you are able to provide a SQL CREATE TABLE definition of the tables in both Access and SQL Server form, it might help us to understand the exact issue you are facing.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 26, 2011 at 6:14 am
Ninja's_RGR'us (7/26/2011)
Let me compound what and SQL MVP and Microsoft employee just told you.There's nothing requiring SQL Server to display the rows in any particular order UNLESS you use order by in ANY AND ALL select queries.
ACCESS <> SQL SERVER. You have to assume different behavior.
Assuming you really have a problem, you got about fixing it by using order bys in the new application (or back in access if it's been converted to an ADP)
Ok then, so I need to write a query for this. I know what column I want to order this one troublesome table by but what would the query look like? Having to use SQL Server was pretty much thrown upon me, I'm only really familiar (and not thoroughly at that) with Access and it's list of basic and logical user-friendly features of which SQL seems to lack.
I apologise if I seem obtuse with this whole thing but SQL Server confounds me so much with it's lack of so many simple things that I've come to take for granted in Access.
SQLkiwi (7/26/2011)
Are the rows explicitly numbered in Access (using AutoNumber, I think)? If so, are the destination tables in SQL Server also endowed with a numbering column (called IDENTITY in SQL Server)? Is it this numbering that you wish to preserve?The wizard you are using uses a SQL Server component called SSIS (SQL Server Integration Services) to load the data. There is an option (hidden away on one of the Options buttons) to enable identity insert - this would preserve the row numbering (assuming the source and destination columns are mapped correctly).
If you are able to provide a SQL CREATE TABLE definition of the tables in both Access and SQL Server form, it might help us to understand the exact issue you are facing.
For all three tables I've given them an ID column in the same manner that SQL does and they are ordered by that column and yes that is the order I wish to keep them in.
July 26, 2011 at 6:17 am
SELECT <column list>
FROM <Table Name>
ORDER BY <Ordering Column>
where the placeholders get replaced by whatever the columns and table are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2011 at 6:18 am
Chazz88 (7/26/2011)
Ok then, so I need to write a query for this. I know what column I want to order this one troublesome table by but what would the query look like? Having to use SQL Server was pretty much thrown upon me, I'm only really familiar (and not thoroughly at that) with Access and it's list of basic and logical user-friendly features of which SQL seems to lack.I apologise if I seem obtuse with this whole thing but SQL Server confounds me so much with it's lack of so many simple things that I've come to take for granted in Access.
I've worked over 5 years with ACCESS / SQL Server combo. Maybe I could consult in. I think you're a little in over your head on this one. Especially if the application is getting refactored.
July 26, 2011 at 6:25 am
Thanks for the offer, I think Gila might have me sorted though. I've been staring at these tables and all these numbers and such for so long my brain has been completely fried. The concept of using a simple query to order the table AFTER it's imported didn't even cross my mind.
I feel like a Grade A moron now 😛
When I get into work tomorrow I'll give it a whirl and let you guys know if that fixes it and *fingers crossed* nothing else goes wrong.
July 26, 2011 at 7:44 am
The thing to keep in mind when comparing Access to SQL Server is that Access has all the same issues, but it hides them and tells you "Pay no attention to the man behind the curtain". Like Word or Excell, it does a lot of that work for you. For people who are accustomed to Access, that's usually a plus. But it's really annoying if the application doesn't actually know better than you what you actually want, but thinks it does. Same reason why professional authors usually work in a text editor, not in Word, and why professional typesetting is never done in Word, but in programs like QuarkXPress, InDesign, et al.
SQL Server doesn't make as many assumptions about what you want, which is a really good thing if what you want isn't what it thinks you want.
It takes slightly more work to get simple things done in SQL Server because of that, but it also allows you to do a whole lot more, that Access can't even approach in terms of complexity and functionality.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2011 at 6:07 pm
Chazz88 (7/26/2011)
The concept of using a simple query to order the table AFTER it's imported didn't even cross my mind.
Everything the others have told you is correct, but I'm not sure that your "problem" has been addressed (although it might have been.) To avoid a deep discussion I'd like to know:
"Why is the order so important?"
Steve.
July 26, 2011 at 7:12 pm
Howdy guys,
All fixed. Thanks for putting up with me and for more info on how SQL works 😛
I didn't end up using the query, what I did was just add an AutoNumber field to the table in Access and then imported it over to SQL via the wizard and it kept everything in perfect order but the query idea was what got my head in the right mindset. So again, thanks.
Fal (7/26/2011)
Everything the others have told you is correct, but I'm not sure that your "problem" has been addressed (although it might have been.) To avoid a deep discussion I'd like to know:
"Why is the order so important?"
Steve.
The second table contains a column called "OccurrenceID" and this links to the "ID" column in the first table. So if the first table is jumbled then obviously the ID's won't match up and thus the incidents won't be classified correctly. e.g. a derailment would end up classified as a door irregularity.
July 26, 2011 at 8:03 pm
Chazz88 (7/26/2011)
Howdy guys,All fixed. Thanks for putting up with me and for more info on how SQL works 😛
I didn't end up using the query, what I did was just add an AutoNumber field to the table in Access and then imported it over to SQL via the wizard and it kept everything in perfect order but the query idea was what got my head in the right mindset. So again, thanks.
Fal (7/26/2011)
Everything the others have told you is correct, but I'm not sure that your "problem" has been addressed (although it might have been.) To avoid a deep discussion I'd like to know:
"Why is the order so important?"
Steve.
The second table contains a column called "OccurrenceID" and this links to the "ID" column in the first table. So if the first table is jumbled then obviously the ID's won't match up and thus the incidents won't be classified correctly. e.g. a derailment would end up classified as a door irregularity.
Okay, now, this is where you pause for a moment. I'm concerned that by attempting to avoid "jumbling up" your rows you may be doing just that.
In SQL Server, the row order is irrelevant. It doesn't matter if the rows ARE jumbled up because SQL Server will match in the value of the ID. In Access I would expect something similar though it's been a while since I did anything meaningful in Access. But the point is that TableA's reference to TablesB's ID should already match. By adding a new column you may inadvertently break that match.
To answer your original question and touch on that deep discussion I alluded to earlier, SQL Server stores data in indexes (to put it simply - there's more to it than that) and it can be stored in more than one. And each index can have a different order. Which index gets used and the underlying HDD activity is what can cause data to come out in a different order.
This is why there's no guaranteed order in the rows, and it's precisely this reason that the order is irrelevant to matching TableA and TableB - only the ID value matters.
If you're checking the 'success' of your import by comparing row order then that is not valid. The important part is checking that TableA and TableB ID's match. Generating a new ID anywhere along the import chain may cause problems. If you need help checking success then you could post the table details for us to assist more directly.
Steve.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply