how to delete records in sql if rowcount exceeds certain row count simultaneously?

Question

im trying to make a log file using sqlserver 2008 database through vb.net.if the row exceeds 1000,then delete top 100 row.everytime the table exceeds 1000 rows,the process will repeat (loop).is it possible to do it?btw,im new to sql.need help with the script

Answer 1

Hi



I am making a few assumptions here, they are:

Your table is called LOG_TABLE

You have a primary key field named 'id'.



Replace thje above with your actual field/tablenames



The following statement will delete the oldest (as per ID field, just order by a different field if you determine which record to delete by a date for example)



delete from LOG_TABLE

WHERE id in (

select top 100 id from LOG_TABLE

ORDER BY 1)

AND (select count(*) from LOG_TABLE)>1000





It would help if I could see the actual table schema (structure) to make sure the query is right for you, but hopefully rhe above will steer you in the right direction. Feel free to send it to me, or post it here.



You will need to run this DELETE periodically from your vb.net app, perhaps after each insert, or a group of inserts into the table.

Answer 2

If you're talking about keeping a data table below 1000 rows, one possible way would be to define a trigger that fires after an insert. The trigger could check the rowcount and delete rows accordingly.



If it's the log file you're trying to keep trimmed down in size, not sure if that's possible.

Answer 3

Sql If Count

Answer 4

The problematical area approximately reproduction removal is retaining purely between the duplicates whilst there is not any longer a thank you to tell apart between them. between the fewer errors-vendors thank you to accomplish that is to make it so which you will tell them aside: upload a column to the table and make it autonumber or some thing comparable. Then, you could create a normal technique alongside the strains of CREATE CURSOR delete_targets AS opt for identity, fname, lname, MIN(newField) AS keepValue FROM someTable team by identity, fname, lname HAVING count extensive sort(*) > a million Then, fetch each and every row of the delete_targets into @identity, @fname, @lname and @keepValue and then: DELETE FROM someTable the place identity = @identity AND fname = @fname AND lname = @lname AND newField > @keepValue when you're achieved, drop the hot column.