Set a limit on the number of rows of a SQL Server table
I would like to set the maximum number of rows a SQL Server table can create. The thing is, I have a program that constantly writes new data to my datatable, but I only need to store one value per column. So I would like to set the maximum number of possible rows for this data type. Does anyone have an idea how to do this?
Otherwise, since I haven't found a way to do this so far, I also thought about rewriting my code and using the SQL UPDATE statement.
I'm not sure which will be better.
+3
source to share
1 answer
Add an id column to your table and limit it to the limit.
create table dbo.MyTable (MyColumn varchar(10), i int identity(1,1) check(i <= 5));
insert into dbo.MyTable
select 'one' union
select 'two' union
select 'three' union
select 'four' union
select 'five';
insert into dbo.MyTable
select 'nope';
Msg 547, Level 16, State 0, Line 7
The INSERT statement conflicted with the CHECK constraint
If it's really just one line, maybe something like this?
create table dbo.MyTable (MyColumn varchar(10), Limited bit not null default(1) unique);
insert into dbo.MyTable (MyColumn)
select 'only one';
insert into dbo.MyTable (MyColumn)
select 'nope';
+1
source to share