Data Selected from a table variable is not in the same order that it was inserted?
I have a table variable with the datatype Varchar(MAX).
I am inserting the value 'header' first, Then based on an inner join, I am next inserting 'Details' into the same table variable. Finally, I am inserting 'Trailer'.
The output which I am getting is in the order: 'header', then 'trailer', and finally 'details'.
My required output is in this order:
Header Detail Trailer
But I am getting this order:
Header Trailer Detail
Note: I am not able to use asc/desc in the selection. I am inserting in the order I need.
A SQL Database does not actually understand what order you put things in, or store the data in a given order. You probably want a second column in your table with a value to use so that whatever order you need is preserved.
Think about it like handing some stuff to your friend to hold - she will have all of it for you later, but she stores it someplace in the mean time. She may move it around while you are not looking to make room for something else, or may hand it back in the same order you gave it to her, but you didn't tell her to keep it in order, so she doesn't.
Databases need to be able to move things around in the background, so the way they are built does not intrinsically know about any order - you need to know the order when you give it to the database, so that you can put it back in the order you want later. The order clause allows SQL to impose an order on the data, but it doesn't remember or have one on its own.
If you don't include an ORDER BY in the outer query, don't expect SQL Server to read your mind. There is no guarantee that SQL Server will return data in the order it was inserted. You should add a column to your table variable called [rank] or something. When you insert the header, insert 1, for the details rows, insert 2, then for the trailer insert 3. Now you can add ORDER BY [rank]; to the end of your query, have predictable results, and everyone is happy.