My torn data pages - Order of things, reloaded.

A while back I wrote about Overridable IDENTITY or the order of things, offering a solution to store data in natural order. This solution worked form me in a couple of projects, until I recently had to reload a table... so the basic procedure would be to;

• Create a new table based on the same definition
• Do an INSERT new_table SELECT ... FROM old_table
• DROP TABLE old_table
• sp_rename 'new_table', 'old_table'

But this does not produce the proper result, as the most recent inserted identity prior to the insert will be applied for the default.

The trick is to make every insert an independent action, so the IDENTITY_CURRENT is properly set for every row; make it run in a cursor. Yeah, I know.... performance, but that pain is largely taken away by making it a FAST_FORWARD. Besides, if anyone has a better suggestion, I'm all ears. So instead of a INSERT ... SELECT, the CURSOR is opened for the selection and the new table is inserted based on the fetched rows.

Altogether in the attached script;

• Create the soon to be table.
• Populate the table.
• Create the new table, pay attention to the default.
• Use the cursor to fill the new table.
• Drop the original table and rename the new table to the name of the original table, don't forget to recreate the default.