SQL FIFO STACK using two tables

Question

I have two fields Position and SerialNumber in a table called FIFOStack. What I need is to pull into this table the most recent 100 serial numbers from my table SerialNum. As a new serial number is populated a stored procedure will pull in the new serial number into the FIFOStack table and a different stored procedure will shift out the oldest entry.

I need some explanation on the process and help writing the stored procedures. Thanks

Answer 1

Instead of constantly managing another table with stored procedures, you could create a view that functions the same way, and only has to do any work when you query it. Example:

CREATE VIEW MyView
AS

SELECT TOP 100 * FROM FIFOStack
ORDER BY Position DESC

So, whenever you need this list, you just do:

SELECT * FROM MyView

And it will always return the latest 100 rows from FIFOStack, without any other changes needing to be made.