I have 2 tables.
CREATE TABLE [dbo].[TABLE_A]([N] [varchar](50) NULL,[varchar](50) NULL,[R] [varchar] (50) NULL,DTime datetime, RUpdate [int])
CREATE TABLE [dbo].[TABLE_B]([N] [varchar](50) NULL,[varchar](50) NULL,[R] [varchar] (50) NULL,DTime datetime, RUpdate [int])
insert into [dbo].[TABLE_A] values ('A','B','C','2020-12-13 12:00',0)
insert into [dbo].[TABLE_A] values ('AA','BB','CC','2020-12-13 13:00',0)
insert into [dbo].[TABLE_A] values ('AAA','BBB','CCC','2020-12-13 14:00',0)
insert into [dbo].[TABLE_A] values ('AAAA','BBBB','CCCC','2020-12-13 15:00',0)
insert into [dbo].[TABLE_A] values ('AAAAA','BBBBB','CCCCC','2020-12-13 16:00',0)
insert into [dbo].[TABLE_A] values ('AAAAAA','BBBBBB','CCCCCC','2020-12-13 17:00',0)
insert into [dbo].[TABLE_A] values ('AAV','BBBFF','CCTCC','2020-12-14 8:00',0)
insert into [dbo].[TABLE_A] values ('AVVAAA','BBGGB','CCFFC','2020-12-14 9:00',0)
There will be more rows that will be added anytime.
Ex:
insert into [dbo].[TABLE_A] values ('ZZZ','XXX','YYY','2020-12-13 13:30',0)
insert into [dbo].[TABLE_A] values ('Z','X','Y','2020-12-13 14:30',0)
Any new records added willl have RUpdate value as 0.
I will be running a sqljob which will is scheduled every 5 mins.It should contain sql
which will transfer records from TABLE_A to TABLE_B and once done it should immediately update the RUpdate column from 0 to 1 so that the next time the sql job runs it moves only those records from TABLE_A to TABLE_B which have RUpdate value as 0).
My SQL Job will have this statement and runs every 5 mins.
insert into [dbo].[TABLE_B]
select * from [dbo].[TABLA_A]
where
convert(date,DTime) = CONVERT(date, getdate())
and cast(DTime as time) > cast(getdate() as time)
and RUpdate <> 1
I would like to update the RUpdate column from 0 to 1 for records which have been moved from TABLE_A to TABLE_B.
TABLE_A will still have the records.
Thanks
one possible way.
update a
set rupdate = 1
output deleted.*
into table_b
from dbo.TABLe_A a
where convert(date,DTime) = CONVERT(date, getdate())
and cast(DTime as time) > cast(getdate() as time)
and RUpdate <> 1
-- confirm results
select *
from table_b
where convert(date,DTime) = CONVERT(date, getdate())
and cast(DTime as time) > cast(getdate() as time)
and RUpdate <> 1
select *
from table_a
where convert(date,DTime) = CONVERT(date, getdate())
and cast(DTime as time) > cast(getdate() as time)
and RUpdate = 1
December 13, 2020 at 11:58 am
thanks
December 13, 2020 at 6:02 pm
You should look at your criteria further - using those functions might cause performance issues and potentially miss data if the agent job does not process for a specific time.
You may be able to just check the RUpdate column and process only those items where RUpdate = 0. To facilitate processing - you can create a filtered index where RUpdate = 0 - which would only ever have rows that need to be processed and might be faster (would need to be tested).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply