| Author |
Topic |
|
kiwikencox
Starting Member
11 Posts |
 Posted - 2005-10-12 : 18:46:49
|
| Hi all,In a stored proc i need to INSERT the results of a SELECT xxxx ORDER BY timestamp into a table and have the results be in the same order as the ORDER BY.So basically...INSERT INTO tblResultsSELECT Name, TimestampORDER BY TimestampNow at this point i was hoping the rows in the tblResults would be in chronological order... but they are not???!!??I need to do this as i then need to add a squential number to the results table which i do with... -- Add sequential number based on ORDER BY above. DECLARE @intCounter INT SET @intCounter = 0 UPDATE tblAuditResults SET @intCounter = IN_SEQ = @intCounter + 1This works, but only if the rows are in the correct order first.So i want to end up with a table with items in chronological order based on Timestamp & with sequential numbers.Q: How do can i get the INSERT to populate the tblResults with row in chronological order???Q: Is there a better way of doing this all together??Cheers,Ken |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-10-12 : 19:01:09
|
| Does tblResults have a datetime column on it?If so, then you "order" the results when you select them using an ORDER BY.An ORDER BY on an INSERT is a strange request. Why does the physical order of the rows in the table matter to you?Nathan Skerl |
 |
|
|
kiwikencox
Starting Member
11 Posts |
Posted - 2005-10-12 : 20:20:13
|
| like i said above... I need to do this as i then need to add a squential number to the results table. This sequential number should represent items in chronological order.I need to get the table in this state as i then run it through a datapump in DTS to create a file. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-12 : 20:33:43
|
| Add a clustered index to your table on the "timestamp" column before you run the UPDATE statement.FYI, "timestamp" is a reserved word in SQL Server, if that is your actual column name, you should consider changing it. Save you a good bit of hassle. |
|
|
|
kiwikencox
Starting Member
11 Posts |
Posted - 2005-10-12 : 20:41:11
|
| thanks for that, will give it a go. Not using "timestamp" as column name, just in example above. |
|
|
|
kiwikencox
Starting Member
11 Posts |
Posted - 2005-10-12 : 20:47:54
|
| that works perfectly, thanks heaps! |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-10-12 : 21:36:48
|
quote:
Originally posted by kiwikencox
like i said above...
Woops! I shouldve read your entire post before replying. Sorry!  Nathan Skerl |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-13 : 10:20:15
|
| I'm curious to know it it is a "given" that an UPDATE will process rows in the order of a clustered index?An alternative would be to insert the new rows (or just their PKs) into a temporary table [using an ORDER BY to ensure chronological order] which also has an IDENTITY column, and then insert them into the actual table [JOINing to the Temp Table] using the Identity Value to provide a suitable offset-value for the IN_SEQ column.Benefit of this is that you do not do both an INSERT and an UPDATE on the main table (with possible consequences for Triggers, and inefficiencies with Indexes, and if you use a TRANSACTION block around INSERT + UPDATE that's making quite a large transaction for the logs - as well as potentially having to "move" all the records around the disk if the change to IN_SEQ column makes the record "bigger". And it avoids the window-of-opportunity for something to access the newly added rows between the INSERT and UPDATE when their IN_SEQ value is invalid.Kristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-13 : 19:54:22
|
| Using the identity column would be better, and there's no guarantee that the update will always work in the order of the clustered index (although my experience has been pretty reliable) I can say, however, that it does NOT work reliably WITHOUT a clustered index.It's also non-standard behavior and may change or be eliminated in future releases. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-14 : 07:25:16
|
| I thought for an Update to a temporary table the order IS a given, but I can't find the reference to back me up!Kristen |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2005-10-14 : 08:55:37
|
The question I would like to know the answer from our Microsoft friends is: Is order guaranteed if I force order by using index hint?I mean, if i have a query:upate t set a = expression from table t (index = index_on_column_b) will data be updated in column b order? I used that approach to implement running totals in the past. Now, I ask developpers to implement it on the client. |
|
|
|
anuj164
Starting Member
49 Posts |
Posted - 2005-10-14 : 11:32:09
|
| Yes the data will be updated as per column b |
|
|
|
|
Insert into & order by...