SQL Server Stored Procedure to dump oldest X records when new records added

I have a licensing scenario where when a person activates a new system it adds the old activations to a lockout table so they can only have their latest X systems activated. I need to pass a parameter of how many recent activations to keep and all older activations should be added to the lockout table if they are not already locked out. I'm not sure how best to do this, i.e. a temp table (which I've never done) etc.

For example, an activation comes in from John Doe on System XYZ. I would then need to query the activations table for all activa开发者_StackOverflow社区tions by John Doe and sort it by DATE DESC. John Doe may have a license allowing two systems in this case so I need all records older than the top 2 deactivated, i.e. inserted into a lockouts table.

Thanks in advance for your assistance.

Something like this perhaps?

insert into lockouts
    (<column list>)
    select <column list>
        from (select <column list>, 
                     row_number() over (order by date desc) as RowNum
                  from activations) t
        where t.RowNum > @NumLicenses

It'd probably be easiest to couple to row_number() over with a view or table-valued function:

WITH ActivationRank AS
(
SELECT SystemId,ProductId,CreatedDate,ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY CreatedDate DESC) AS RANK     
FROM [Activations]
)

SELECT SystemId, ProductId, CASE WHEN RANK < @lockoutParameterOrConstant 0 ELSE 1 END AS LockedOut
FROM ActivationRank

Before you invest time to read and try my approach, I want to say that Joe Stefanelli's answer is an excellent one - short, compact, advanced and probably better than mine, espacially in terms of performance. On the other hand, performance might not be your first concern (how many activations to you expect per day? per hour? per minute?) and my example may be easier to read and understand.

As I don't know how your database schema is set up, I had do to some assumptions on it. You probably won't be able to use this code as a copy and paste template, but it should give you an idea on how to do it.

You were talking about a lockout table, so I reckon you have a reason to duplicate portions of the data into a second table. If possible, I would rather use a lockout flag in the table containing the systems data, but obviously that depends on your scenario.

Please be aware that I currently do not have access to a SQL Server, so I could not check the validity of the code. I tried my best, but there may be typos in it even though.

First assumption: A minimalistic "registered systems" table:

CREATE TABLE registered_systems 
   (id INT NOT NULL IDENTITY, 
    owner_id INT NOT NULL,
    system_id VARCHAR(MAX) NOT NULL, 
    activation_date DATETIME NOT NULL)

Second assumption: A minimalistic "locked out systems" table:

CREATE TABLE locked_out_systems 
   (id INT NOT NULL, 
    lockout_date DATETIME NOT NULL)

Then we can define a stored procedure to activate a new system. It takes the owner_id, the number of allowed systems and of course the new system id as parameters.

CREATE PROCEDURE register_new_system 
   @owner_id INT, 
   @allowed_systems_count INT, 
   @new_system_id VARCHAR(MAX)
AS
BEGIN TRANSACTION
   -- Variable declaration
   DECLARE @sid INT  -- Storage for a system id

   -- Insert the new system
   INSERT INTO registered_systems 
      (owner_id, system_id, activation_date)
   VALUES
      (@owner_id, @system_od, GETDATE())

   -- Use a cursor to query all registered-and-not-locked-out systems for this
   -- owner. Skip the first @allowed_systems_count systems, then insert the
   -- remaining ones into the lockout table.
   DECLARE c_systems CURSOR FAST_FORWARD FOR
      SELECT system_id FROM
         registered_systems r
      LEFT OUTER JOIN 
         locked_out_systems l
      ON r.system_id = l.system_id
      WHERE l.system_id IS NULL
      ORDER BY r.activation_date DESC

   OPEN c_systems

   FETCH NEXT FROM c_systems INTO @sid

   WHILE @@FETCH_STATUS = 0
   BEGIN       
       IF @allowed_systems_count > 0
          -- System still allowed, just decrement the counter
          SET @allowed_systems_count = @allowed_systems_count -1
       ELSE
          -- All allowed systems used up, insert this one into lockout table
          INSERT INTO locked_out_systems 
             (id, lockout_date)
          VALUES
             (@sid, GETDATE())

       FETCH NEXT FROM c_systems INTO @sid
   END

   CLOSE c_systems
   DEALLOCATE c_systems

COMMIT