Solved

Delete top rows from SQL Server Everywhere

Posted on 2006-10-26
10
515 Views
Last Modified: 2008-01-16
I use a database table as a log. For simplicitys sake here's a scaled down version:
CREATE TABLE Log (
Date datetime NOT NULL PRIMARY KEY,
Title nvarchar(255) NOT NULL
)

Problem: I don't want the log to contain more than 11.000 items. When log goes over that, it should delete 1.000 rows from the top.

Question: How do I delete the first 1.000 rows (oldest entries) from this table in SQL Server Everywhere?

Limitations: SQL Server Everywhere does not suppport triggers, stored procedures, TOP n or SET ROWCOUNT.

Thanks.
0
Comment
Question by:halligang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 17

Expert Comment

by:HuyBD
ID: 17810583
You can add the below query after insert new record or schedule in your application
delete from Log  where Date not in (select top 11000 Date from Log  order by Date desc)
0
 
LVL 23

Expert Comment

by:adathelad
ID: 17810597
Hi

>> Limitations: SQL Server Everywhere does not suppport triggers, stored procedures, TOP n or SET ROWCOUNT
Makes it tricky :)

I'd suggest an alternative, whereby you delete rows that are over x days/weeks/months/years old.
This would allow you to use something like:
DELETE FROM Log WHERE Date < (DATEADD(m, -6, GETDATE()))
(This example deletes things over 6 months old).

You could then schedule this to run as a job every day.

0
 

Author Comment

by:halligang
ID: 17810847
HuyBD: thanks, but TOP is not a keyword in Everywhere edition

adathelad: thanks, but size is an issue, since some days may add a significant amout of log entries while others may produce none


A solution I've been thinking about, but didn't quite complete (since stored procedures is not an option):

Add an Identity column, Id

Then, at regular intervals make three calls to the database
SELECT MIN(Id) from Log
SELECT MAX(Id) from Log

In my program code:
int tableRows = max - min;
if (tableRows > 11000)
{
int idLimit = max - 10000;
(create query:
DELETE FROM Log WHERE Id < idLimit;
}

This may be a hint for a solution. Two problems with the above:
1. Id column will eventually wrap. How can this be handled? A solution would be to copy all contents to a new table which effectively reset the value, but will be somewhat inefficient.
2. Failed inserts will cause the identity column to skip over several values.

Thanks.
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 1

Expert Comment

by:b2mje112
ID: 17811180
1.  select count(*) from Log
2.  Not sure how to do it if ID wraps.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 17812461
You will need the id column, so go ahead and add it.  You will also need a work table (temp table would be best if available) with an ident column, like so:

CREATE TABLE workTable (  --or #workTable
    ident INT IDENTITY(1,1),
    id INT  --from main table  
    )

Then:

IF (SELECT COUNT(datetime) FROM tableName >= 11000)
BEGIN --{
    TRUNCATE TABLE workTable  --or DELETE FROM
    INSERT INTO workTable (id)
    SELECT id
    FROM tableName
    ORDER BY datetime
    DELETE FROM tableName
    WHERE id IN (SELECT id FROM tableName WHERE ident <= 1000)
END --}
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17812474
Almost forgot, you should add an index on (datetime, id) on tableName.
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 17816685
get something from ScottPletcher' idea

create table #tmp(ID int identity(1,1), date int,title varchar)
insert into #tmp(date,title) select * from Log
delete  from #tmp where id<1100
delete  from Log
insert into Log select date,title from #tmp
drop table #tmp

//you can check record count

create table #tmp(ID int identity(1,1), date int,title varchar)
insert into #tmp(date,title) select * from Log
if exists (select 1 from #tmp where id>1100)
 begin
   delete  from #tmp where id<1100
   delete  from Log
   insert into Log select date,title from #tmp
 end
drop table #tmp
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17906461
Thanks!  But, D'OH, I left one thing out:


IF (SELECT COUNT(datetime) FROM tableName >= 11000)
BEGIN --{
    TRUNCATE TABLE workTable  --or DELETE FROM
    INSERT INTO workTable (id)
    SELECT TOP 1000 id     --<<--
    FROM tableName
    ORDER BY datetime
    DELETE FROM tableName
    WHERE id IN (SELECT id FROM tableName WHERE ident <= 1000)
END --}
0
 

Author Comment

by:halligang
ID: 17913784
Thanks. I built on your solution. But the final comment didn't follow the rules... TOP is not allowed on SQL Server Everywhere... :-D
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17915786
OK; technically it's not needed -- it would just be more efficient to insert only the first 1000 instead of all of them.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question