I'm writing a conversion tool that deletes all records in all tables in the destination database (SQL Server 2000) first. It then pumps all records from the source database (Access 97) into the destination database, using SET IDENTITY INSERT ON and SET IDENTITY INSERT OFF. I do this table by table. After all records of a table have been inserted, I have to reindex the table's ID
For example: A table in my destination database has the following records
Considering no ID's >10 have been deleted, the current new index would be 11.
After conversion, this table can have the following records:
Now I want to reindex this table, so that the next new index (ID) would be 4.
I can't seem to manage this. The new index will remain 11. I've tried DBREINDEX, but this doesn't work. I have a lot of tables that need to be converted like this, so I don't want the conversion program to delete all indexes and create them again. It'd cost me a lot of time examining all current indexes and then writing code to drop and create them.
I can't use the SQL Server 2000 Import/Export tool to do this, because additional logic is needed in order to have a succesfull conversion.
Thanks in advance,