CODEWITHSUNDEEP
sql-serversql-server-2005
Denny Sutedja
Denny Sutedja

Reputation: 538

How To Set Limit Table Row SQL?

How to set limit table row in SQL Server?

I want to set limit of my table rows to 100 rows only.

So when the table have more than 100 rows, I want to delete first row then add new row to last row (100).

How can I do this?

Upvotes: 2

Views: 353

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

I think you hv to do two things i) Create Trigger

declare @MaxRowLimit int=5
declare @t table(col1 int)
insert into @t values(1),(2),(3),(4),(5)

insert into @t VALUES(12)

;With CTE as
(


select top (@MaxRowLimit) col1 

from @t t1
order by t1.col1 desc
)
,CTE1 as(
select * from @t t
where not exists
(select  col1 

from cte t1 where t.col1=t1.col1
)
)
delete from cte1
select * from @t

ii) If it is bulk insert then,you hv to do manipulation before bulk insert. like if bulk insert count is greater than 100 then sort and keep last 100 rows and remove rest rows.

Upvotes: 2

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

One thing that i can assure you..

  1. Create a trigger that if > 100 then delete first record record.

see here as your guide.

Upvotes: 2

Related Questions