April 29, 2013 at 7:13 am
Hello!
I am importing data from a source system running IBM iSeries. I use OPENQUERY to query the source system.
The source system is a bit peculiar, so in table tblModelsAccount I have the following fields (my example is simplified)
AccountNo ModelCode
12345 A
12345 B
So which one is the current ModelCode for AccountNo 12345? Well, the not so obvious business rule is that the first one is always the current. That means A.
So I import all rows to a table I have in my SQL Server DB. Like this
INSERT INTO MYDATABASE.dbo.ModelAccounts(AccountNo, ModelCode)
SELECT * FROM OPENQUERY(SourceDSN,'SELECT AccountNo, ModelCode FROM tblModelsAccount ')
Then I do a Select from my import table
SELECT * FROM MYDATABASE.dbo.ModelAccounts
Interesting enough, the rows have shifted places.
AccountNo ModelCode
12345 B
12345 A
Obviously it is hard to use the rule "first one is the current", when this happens.
But exactly what is deciding the order of rows in the SQL Server table when doing an insert like this?
I would have expected the rows to be ordered like in the source system.
April 29, 2013 at 7:34 am
Clark,
Welcome to the board. Please google for set theory. Basically, in all RDBMS a group of records (a set) has no inherent order: You cannot guarantee that the same set will always be returned in the same sequence unless you specify it; this may because of a database cleanup, an change to the query plan or any number of reasons. If you want records in a specific order you need to ask for them (the cororally of this is DON'T ASK FOR THE ORDER IF YOU DON'T NEED IT)
If you want the record with the lowest suffix you need to do
SELECT AccountNumber, MIN(AccountSuffix) FROM myTable GROUP BY AccountNumber
You may then need to join this back to the source table again to access other fields.
SELECT
T1.*
FROM
myTable T1
JOIN
(
SELECT
AccountNo,
MIN(ModelCode) AS 'LiveModelCode'
FROM
myTable
GROUP BY
AccountNo
) X on X.AccountNo = T1.AccountNor and X.LiveModelCode = T.ModelCode
NOTE: There are lots of different way to do this sub-query. My preferred method is as a Common Table Expression but if you are new to T-SQL this may be confusing to you.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply