I am importing data from Excel into a SQL Server 2016 database using the SQL Server Import & Export Data feature. After that I need to address the 1st and 3rd record to retrieve specific information (that the Data Dump of our ERP places there). The order criteria should be that of the time the record had been inserted in the table.
I know that this officially does not exist, but I wonder why the table is always displayed in the same order if you do not specify an order, so somewhere - internally (?) - there must be that information stored.
I don't see where to place a autoincrementing primary key during importing the data. That would solve the problem too.
It even looks good if I add a dummy order criteria (order by 1) but I don't know if that is reliable.
I will do the following, gambling that this will work always. Though I can't use "order by 1" in the row_number expression, I define a parameter and then its accepted.
declare @n int = 1
select * from (
select *, ROW_NUMBER() over (order by @n) as ord from ImportedData)x
where ord = 3
2条答案
按热度按时间gxwragnw1#
If you want to "gamble", here's your explanation: Usually SQL Server will write the data serially to a disk. It will also usually retrieve them in the same order when you
selectthem withoutorder by.Gambling is, of course, bad practice. You are the judge of whether the convenience it provides is worth the probability of things going south and the designer appearing incompetent. It's a mindset, as well - as a database designer, in my opinion, you should strive more for "correct" and less for "easy", potentially making people dissatisfied in the short run - no one is going thank you for something that DIDN'T break because of good practices.
9q78igpj2#
The solution to this problem is to add a sequence number to the table that populates as part of the insert.
I don't see where to place a autoincrementing primary key during importing the data.
Depending on how you've set this up, you could do it ahead of time by adding the column in Excel, or by changing the table definition to add an identity column or other column using the
next valuein a sequence as the default value, or some combination thereof.I know that this officially does not exist
Correct. THERE IS NO SUCH THING AS INSERTION ORDER!
... but I wonder why the table is always displayed in the same order if you do not specify an order
Because you've been lucky. Without an
ORDER BY, the database can and will show records in a different order from one run of the same query to the next, and there are many reasons this can happen. To name a few:Most of these things are uncommon, but any one could surprise you at an inopportune moment.