Solved

Delete top rows from SQL Server Everywhere

Posted on 2006-10-26
10
507 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

732 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