Let's say I want to delete all records in a SQL Server table that were inserted over three months ago and keep the records inserted since then. We're dealing with many records on both sides of the three month period so I decided instead of running a DELETE with WHERE clause statement I will just insert everything into a temporary table, truncate the original table, and then insert all records from temporary table back into the original table.
But I want to keep all of the values from the Primary key ID column which are used by a Foreign key in another table. How can I keep from resetting the seed value as well as inserting everything where the ID column is the same for each record?
Drop the foreign Key and disable identity increment from the original table. Then do the above process you mentioned. That is
10045 - corresponds to number of rows in temp table. So that identity seed start from 10046.
Hope this helps.