mcertini (5/5/2013)
I am having problems ordering records to go into another table. The following is my code:
USE
EURUSD
GO
SELECT *
INTO NEWTABLE
FROM OLDTABLE
ORDER BY RECORD
After processing into another table I still have a wrong starting record and gaps in the continuous records. When I process a SELECT TOP 1000 query I am expecting to see the first record proceeding through the 1000th. record. Instead I am seeing record 8625 forward. How do I create a query to create a new table with the proper sequential ordering?
This will create a new column using the special system IDENTITY function which works when doing SELECT INTO. Your rows will maintain their original keys but will now have a new sequentially numbered column.
SELECT
IDENTITY(INT,1,1) AS ID,
OLD.*
INTO NEWTABLE AS NEW
FROM OLDTABLE AS OLD
ORDER BY RECORD
Second option...this will also create a sequentially ordered column based on your sort criteria which gives you more flexibility on the sorting.
SELECT
ROW_NUMBER() OVER (ORDER BY OLD.RECORD, OLD.COL1, OLD.COL2 [etc]) AS ID,
OLD.*
INTO NEWTABLE AS NEW
FROM OLDTABLE AS OLD
There's no guarantee that when you insert large amounts of data into a table (especially an existing table) that the rows will be in physical order. If you create a pseudo-key like this for ordering you will still need to apply an ORDER BY to be sure of getting results back in your desired order.
If you really need the rows in physical order in the file you can drop all indexes and re-create a clustered index on the columns to be ordered. The index process will then by definition re-order the data physically based on whatever column definitions you define for the clustered index.
If for some reason your table is using uniqueidentifiers (GUIDs) as a sort key, there is a new SequentialGUID datatype available which can be used to keep your rows in sequential order. Otherwise, trying to sort based on a GUID's value is somewhat pointless.