Procedure to delete the old rows leaving behind bottom 100 rows automatically(first in first out) when data is loading from scada to sql

Question

how to do first in first out in SQL Server 2008

USE [TEST2]
GO
/****** Object:  StoredProcedure [dbo].[usp_chartData8]    Script Date: 05/24/2010 14:30:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_chartData8]
As
begin
declare @rowcount as integer
declare @deletecount as integer
declare @Totalrows as integer
set @Totalrows=10
select @rowcount=count (*) from tblBarcode;
IF (@rowcount > @Totalrows)
begin
select @deletecount=@rowcount-@Totalrows
print @Totalrows
print @rowcount
print @deletecount
delete top (@deletecount) from tblBarcode
End
End
--select * from tblBarcode
--EXEC usp_chartData8

when i run this then only it shows 10 rows i want to update automatically from SCADA to SQL

Answer 1

You need to get your head around the logic.... try using traditional methods - work on a paper and then implement.

Another suggestion - If you are deleting too many rows than use a bigger batch size else transaction logs will get filled up.

USE [TEST2];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[usp_chartData8]
AS
     BEGIN
         DECLARE @rowcount AS INTEGER;
         DECLARE @deletecount AS INTEGER;
         DECLARE @Totalrows AS INTEGER;
         SET @Totalrows = 10;
         SELECT @Totalrows = COUNT(*) -- Changed from @rowcount
         FROM tblBarcode;
         IF(@Totalrows > @rowcount ) -- Changed
             BEGIN
                 SELECT @deletecount = @Totalrows - @rowcount; -- Changed
                 PRINT @Totalrows;
                 PRINT @rowcount;
                 PRINT @deletecount;
                 DELETE TOP (@deletecount)
                 FROM tblBarcode;
             END;
     END;