Is it possible to create a view such that when it is run via a schedule, it only returns rows that have not been in any previous selects of that view? Coming from other systems I would see this as a repository to store results when the schedule is run, then exclude anything in the repository on subsequent runs. For MSSQL though, I imagine there are more clever ways of tackling this issue.

CodePudding user response：

You have 2 options:

1. record the date and time when you make run the query and then run

select * from myView where dateTimeColumn > date_and_time_when_last_run;

2. use a stored procedure which notes the last record retrieved

CodePudding user response：

This not a "View" but you can use a flag to denote 'read', OUTPUT clause to return the columns to simulate what you want.

-- sample table
create table tbl
(
    ID int identity,
    flag bit default 0,
    DT datetime
);

-- insert 2 rows of sample data
insert into tbl(DT) values
(getdate()),
(getdate());

-- update the table set the flag and OUTPUT the columns
update tbl
set    flag = 1
output inserted.ID, inserted.DT
where  flag = 0;

Result:

-- insert 3 more rows
insert into tbl(DT) values
(getdate()),
(getdate());

update tbl
set    flag = 1
output inserted.ID, inserted.DT
where  flag = 0;

Result: