Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
leahsmart
Posting Yak Master
133 Posts |
 Posted - 2004-05-19 : 04:45:51
|
Hi Reader!I am copying a load of data from a view into a temporary table using the below code:EXEC ('INSERT INTO #tblTempvwScores SELECT * FROM vw__DGW_Scores ORDER BY DateAssessed')The above statement is a simplified version of my code.I need the data ordered by the DateAssessed but it does not appear to be ordering all of the data, there are a few random rows in the wrong place.Does any one have any ideas? I cannot order the data when I call it as I am using a cross tab sproc and I do not want the DateAssessed field on it so I have to order it before then.Any help would be great.ThanksLeah |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-19 : 04:49:33
|
| I assume you want an identity ordered otherwise a clustered index after the insert would do.Afraid it;s not possible. Tables are unordered and sql server doesn't honour the order by on inserts.In you have multi processors you might be better using maxdop=1.Another option is - insert into temp table, add clustered index - insert to final temp table.That's still not guaranteed but I've never seen it fail.Best to put the sequence on using a query if that's what you want.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
leahsmart
Posting Yak Master
133 Posts |
Posted - 2004-05-19 : 04:58:35
|
| How would I go about using an Index? Do you have any example code. I have looked in the help and it has confused me. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-19 : 05:55:38
|
| create clustered index ix on #tblTempvwScores (DateAssessed)Why do you need the data ordered?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
leahsmart
Posting Yak Master
133 Posts |
Posted - 2004-05-19 : 06:11:11
|
| Thanks so much for your help. I have got it working now. Thanks. |
|
|
|
|
|
|
Ordering an insert into a temporary table