0

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
James Cahours
  • 85
  • 2
  • 8
  • Don't delete...never delete. Add a 'active' flag or something to that extent to mark the record as active or deleted. – Twelfth Feb 11 '14 at 23:34
  • I'm deleting so that we can free up room in our db. Boss doesn't want to pay to hold 20 million ad impression logs that clients rarely look at. Therefore I'm taking everything older than 3 months and inserting it into a table that holds total impressions and hits for each ad. Should free up a few GBs on server. – James Cahours Feb 12 '14 at 00:15
  • If you're referencing this table from another table, are you also deleting the references to the old rows? – Jon Senchyna Feb 12 '14 at 01:31
  • There are no references to this table – James Cahours Feb 12 '14 at 16:48

2 Answers2

2

Drop the foreign Key and disable identity increment from the original table. Then do the above process you mentioned. That is

  1. Insert records into temp table - (insert - select whatever you want and also foreign key rows present in another table).
  2. Truncate original table.
  3. Insert records from temp table to original table (if there are lot of records, drop original table and rename temp table to original table name).
  4. Add foreign key constraint and enable identity property
  5. reseed the identity. - DBCC CHECKIDENT (yourtable, reseed, 10045)

10045 - corresponds to number of rows in temp table. So that identity seed start from 10046.

Hope this helps.

SNathan
  • 186
  • 2
  • 4
  • Will the identity seed I get from CHECKIDENT get the number of rows in temp table or get the latest ID column value? I need the new seed to be the largest value in ID column not the number of rows. – James Cahours Feb 12 '14 at 00:21
  • You pass in the seed value to the `CHECKIDENT` function. You can select the `MAX` identity value from your table after the insert and then pass that in to `CHECKIDENT` to get your desired functionality. – Jon Senchyna Feb 12 '14 at 01:29
0

Try:

SET IDENTITY_INSERT OLD_TABLE ON;
GO
INSERT INTO OLD_TABLE ( ... columns, including ID ... )  
     SELECT ( ... columns, including ID ... )  FROM TEMP_TABLE;
albe
  • 551
  • 4
  • 15