November 11, 2008 at 1:03 am
I fill a table with values from two existing tables, using INSERT INTO, which works fine.
But the table is filled in the order of the connecting field, and I want it in a more natural order.
I.e. Table1ID or CompletedDate. And the ORDER BY does not function.
How is the done?
Used SQL:
[font="Courier New"]INSERT INTO
FieldNames
SELECT FieldNamesTable1, FieldNamesTable2
FROM Table1 T1
JOIN Table2 T2 ON T1.ConnectField = T2.ConnectField
ORDER BY T1.AFieldName --does not function (no errormessages, just not used)[/font]
November 11, 2008 at 1:33 am
Order by is ignored in an insert, except for it's effect on the identity columns. Order of data in a table is a meaningless concept. Tables are unordered sets. If you want the data returned in a specific order when you query the table, use an order by in that query.
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
November 11, 2008 at 2:06 am
If you have a clustered index on the table data will be ordered according to the clustered index.
"Keep Trying"
November 11, 2008 at 2:13 am
Gail thanks for your answer.
But there is a reason for the order in the new made table.
What I forgot to mention is that the inserted table has an autoincrement (PK clustered) field.
(that is how I found out the order of insertion).
And that I normally present the records of the new made table in that order.
Is'nt it possible to insert the records in a certain order?
I could use a cursor, I think, but being a 'SET orientated' person I don't know how.
November 11, 2008 at 2:55 am
The order by should guarantee the order of the identity values, not the physical insert order. I don't have a ref for that right now, just a comment by one of the Query Optimisation team. I'll test this out later and see what happens.
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
November 11, 2008 at 3:25 am
Gail, thank you for your effort.
I hope your test gives us a solution 🙂
I use SqlServer 2005
November 11, 2008 at 11:28 am
I have used this before when trying to insert records in a specified order and wanting the Clustered PK Identity field to sort accordingly:
Insert into MyTable (col1, Col2, col3)
(Select Col1, Col2, Col3
from OtherTable)
Order by Col2
Please note, though, that previous comments in this post are valid in regards to how your indexes are set up. I only use the above when I'm pulling into Temp tables as "presort" to further operations I plan on doing.
November 11, 2008 at 1:45 pm
I can confirm that the identity order is definitely that imposed by the ORDER by during the insert. And since the autoincrement (or identity) column is the primary key, there's a decent chance that that is now your physical order (since primary key defaults to being the clustered index unless there already is a clustered index on the table).
That being said. that doesn't mean you won't find some fragmentation in your clustered index (just because a row was assigned 1 as the ID doesn't mean it was the first row inserted, etc... so there may have been some page splits).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 12, 2008 at 4:03 am
Sorry to tell you, that your suggestions did'nt work
I tried
[font="Courier New"]INSERT INTO
FieldNames
(SELECT FieldNamesTable1, FieldNamesTable2
FROM Table1 T1
JOIN Table2 T2 ON T1.ConnectField = T2.ConnectField)
ORDER BY AFieldName -- (not used, order is ConnectField)[/font]
and
[font="Courier New"]INSERT INTO
FieldNames
SELECT * FROM
(SELECT TOP 100 PERCENT
FieldNamesTable1, FieldNamesTable2
FROM Table1 T1
JOIN Table2 T2 ON T1.ConnectField = T2.ConnectField
ORDER BY T1.AFieldName)Tmp --(not used, order is ConnectField)[/font]
I think it is impossible 😉
November 12, 2008 at 4:14 am
There is no such word as "impossible". @=)
Okay, forget about the INSERT part of the statement for the moment. Run just the SELECT statement by itself. What do you get?
Also, give us table structure for all 3 tables, including indexes (ALL indexes), and some sample data please. We'll be able to help you better.
November 12, 2008 at 6:07 am
Brandie Tarvin (11/12/2008)
There is no such word as "impossible". @=)
Brandie, OK, you're right. 🙂
But there was a stupid me, looking at the cache with the old data. I'am so ashamed 🙁
So, both
[font="Courier New"]INSERT INTO
FieldNames
(SELECT FieldNamesTable1, FieldNamesTable2
FROM Table1 T1
JOIN Table2 T2 ON T1.ConnectField = T2.ConnectField)
ORDER BY AFieldName[/font]
and
[font="Courier New"]INSERT INTO
FieldNames
SELECT * FROM
(SELECT TOP 100 PERCENT
FieldNamesTable1, FieldNamesTable2
FROM Table1 T1
JOIN Table2 T2 ON T1.ConnectField = T2.ConnectField
ORDER BY T1.AFieldName)Tmp[/font]
did the trick.
The data are now in the correct order in the ID (the clustered PK).
Thank you 😎
November 12, 2008 at 6:11 am
You're welcome. @=)
Glad we could help you figure it out.
November 12, 2008 at 6:35 am
Henk Schreij (11/12/2008)
The data are now in the correct order in the ID (the clustered PK).Thank you 😎
Excellent.
Just be aware that doesn't mean that the data will be returned in that order if you do a select without an order by. It might be, it might not be, it depends on what plans the query optimiser picks.
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
November 13, 2008 at 6:56 am
how about using a table variable, inserting your data in there, then inserting the sorted table variable into the permanent table?
November 13, 2008 at 7:01 am
Seggarman,
Even following your suggestion, Gail's point is still valid. Just because the data is stored in a table in a certain physical or logical order does not mean that's how it will get returned when you do your SELECT statement.
It all depends on how complicated your SELECT is, whether you're using GROUP BY, what else you might be joining to, data partitions, etc. Hence the optimizer might still (even on a simple SELECT) return the data differently than you expect.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply