Limit row a table in SQL and Insert new rows on Top

Question

I have a SQL Server database table : History_KH
It look like:

Now I want to limit row number of this table (ex : 200 rows)
And when I insert new rows from code, It will insert on top of table. That's mean : Insert new rows on top of database table and the old rows over of 200 counts will be delete.
Please support me.

Answer 1

You can't really limit a table for a fix number of records but you can get rid of unwanted records. Assuming you are on SQL-Server

--STEP1 :Do your insert here

--STEP2: Delete older records over 200 ordering by dateTime column
;WITH CTE AS (

  SELECT *, ROW_NUMBER() OVER (ORDER BY DateTime DESC) rn 
  FROM YourTable
)
DELETE CTE WHERE rn > 200