Stored Procedure to remove all records that don't have max FileID

Question

Post reply

Stored Procedure to remove all records that don't have max FileID

vecerda

SSCrazy

Points: 2532
More actions
January 6, 2014 at 1:45 pm

#281759

I have a SQL 2012 database that has 10 tables. One of the tables is populated by manual import from CSV file. Each time a user calls custom ASP.NET code., records get inserted into a table called forecast_data with incremental increase in FileID. So first import has FileID of 1, second import has FileID of 2 etc.
Structure:
TABLE [dbo].[forecast_data](
[recID] [bigint] IDENTITY(1,1) NOT NULL,
[FileID] [int] NULL,
[Project_Name] [nvarchar](255) NULL,
[Stoplight_Status] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[Region] [nvarchar](255) NULL,
[Brand] [nvarchar](255) NULL,
[Franchise_Fee] [float] NULL,
[Store_Opens_Baseline] [nvarchar](255) NULL,
[Store_Opens_Forecast] [datetime] NULL,
[Store_Opens_Actual] [datetime] NULL,
[SAR_Accepted] [datetime] NULL,
[Construction_Started] [datetime] NULL,
[Lease_Signed] [datetime] NULL,
[Closed_Date_Baseline] [datetime] NULL,
[Closed_Date_Forecast] [datetime] NULL,
[Closed_Date_Actual] [datetime] NULL,
[Status] [nvarchar](50) NULL,
[Relocation] [nvarchar](50) NULL,
CONSTRAINT [PK_forecast_data] PRIMARY KEY CLUSTERED
What I am trying to do is only keep the data that has the highest FileID (MAX(FileID). I would like to write a store procedure that removes all older data once a new import is written into the table.
Any help/suggestion will be greatly appreciated. Thanks in advance,
Petr

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

You could truncate the table first, then load the data.

Alternatively

;WITH CTE AS

(

SELECT *

,RN = DENSE_RANK() OVER (ORDER BY FileID DESC)

FROM [dbo].[forecast_data]

)

DELETE CTE

WHERE RN > 1

(This actually deletes from the table.)

vecerda SSCrazy Points: 2532 More actions · Answer 2

This will work great. thank you very much for your help,

Petr

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

petr.caslavka (1/7/2014)
This will work great. thank you very much for your help,
Petr

Which option?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vecerda SSCrazy Points: 2532 More actions · Answer 4

The CTE solution will work great. This forum is such a terrific resource for folks like me that are somewhat new to SQL.

thanks for everything,

P0etr

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Truncating the table before loading new data would be more efficient - is there some reason why you chose the CTE instead?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vecerda SSCrazy Points: 2532 More actions · Answer 6

the import is manually triggered by user from an external site. After the import the data is manipulated. I think that instead of using truncation, I will eventually move the old data to a backup table. It is pulling only 1400 records and it takes less than a second to complete so the performance is not a really an issue. Both of the solution are good solutions.

thanks,

Petr