Assuming you have NO FK's referencing the table...
CREATE TABLE NEWTABLE (COLNAME INT IDENTITY(1,1), NEXT_COLUMN .....)
SET IDENTITY_INSERT NEWTABLE ON
INSERT INTO NEWTABLE (LIST ALL COLUMNS) -- listing all columns but the identity
SELECT *
FROM TBL_NAME
ORDER BY {YOUR_IDENTITY}
SET IDENTITY_INSERT NEWTABLE OFF
IF ((SELECT COUNT(*) FROM NEWTABLE) = (SELECT COUNT(*) FROM TBL_NAME))
DROP TABLE TBL_NAME
exec SP_RENAME 'NEWTABLE', 'TBL_NAME
Without knowing your column layout it won't be exact, but above lists the general idea of the process you would follow to complete the task. Please feel free to ask any questions.
Main Topics
Browse All Topics





by: rafranciscoPosted on 2005-08-29 at 17:52:38ID: 14780663
You cannot make an existing column an identity column. You have to add a new column for that.