Hi.
Can someone help here please
I have a table in my SQL server database which is written to at various points in time (from my C# program - decoding a large file).
The problem is that some of my sorts in conjunction with an INNER JOIN to itself should be able to return data in a sorted order (sorted by 3 columns).
My table has a PRIMARY KEY called LineIndex which is an identity column, seeded to start with the first line and the data is added to it. What I need to do now is to take my table, re-sort it and store it back in the same table with the correct sort.
What I need to do is re-sort the contents of my table into a specific order and re-seed the LineIndex to start from 1 again.
What I do so far is this:
1. Create a temporary table #TEMPDEST which has exactly the same properties of the table I want to re-sort
2. INSERT the contents of my original table into #TEMPDEST (all except the LineIndex column) into #TEMPDEST, performing the sorts as it stores (I've browsed the #TEMPDEST table in MSSMSE database browser, it looks good)!.
3. Delete the contents of the original table (not using drop - the table will be re-populated) using DELETE FROM Table_Name
4. Reset the LineIndex column identity in my table by performing "dbcc checkident(Table_Name, RESEED, 0)
5. INSERT the sorted table from #TEMPDEST back into it's original table (which theoretically should be sorted).
6. Drop the #TEMPDEST table.
It would appear that the sorted data becomes unsorted when it is copied back into the original table.
Can anyone tell me why this method isn't working please
Please don't ask why the table needs re-sorting. There are lots of data manipulation within other functions and single-sorting via the database doesn't always return the desired result. Other functions need to do further sub-sorts for their own use, but the initial sort I want here is necessary!
Sean.