CODEWITHSUNDEEP
sql-serversql-server-2016scdtemporal-tables
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5767

Remove repeating interval/history duplication

It's a bit a challenge to put the proper Title to that question, so I'll try to explain the problem. We have Oracle --> SQL Server replication using JCC. The way it works, that it reads the transaction log in Oracle and then with every change it sends 2 statements: INSERT + UPDATE. So if there are no such record it will be inserted if the record in the SQL Server exists already, so the first INSERT will fail because of PK violation and then it will try to UPDATE the record.

Now we are trying to use SQL Server temporal tables for the history tracking. The problem starts when the JCC (replication) fails as we need to re-play the logs. For example, replication failed at 11:01AM. To be sure that we will not miss any records, we need to replay for example from 10:45AM. It works well for normal tables, but when we deal with temporal ones then it just craps the history and the history becomes inaccurate.

So, now the question. How can identify these records and cleanup them?

enter image description here

CREATE TABLE #temptable ( [UPDATE_DATE] datetime, [SysStart] datetime2(7), [SysEnd] datetime2(7), [key_column] char(8) )
INSERT INTO #temptable
VALUES
( N'2017-10-23T17:21:34', N'2017-10-23T21:21:44.5514785', N'2017-10-25T10:24:23.604867', '41862277' ), 
( N'2017-10-23T16:21:43', N'2017-10-25T10:24:23.604867', N'2017-10-25T10:24:24.4954892', '41862277' ), 
( N'2017-10-23T16:49:02', N'2017-10-25T10:24:24.4954892', N'2017-10-25T10:24:25.5111274', '41862277' ), 
( N'2017-10-23T17:20:58', N'2017-10-25T10:24:25.5111274', N'2017-10-25T10:24:25.5111274', '41862277' ), 
( N'2017-10-23T17:21:34', N'2017-10-25T10:24:25.5111274', N'2017-10-25T10:24:25.5111274', '41862277' ), 
( N'2017-10-10T14:17:49', N'2017-10-20T14:25:24.7135228', N'2017-10-23T20:21:52.6602561', '41862277' ), 
( N'2017-10-23T16:21:43', N'2017-10-23T20:21:52.6602561', N'2017-10-23T20:49:08.6122864', '41862277' ), 
( N'2017-10-23T16:49:02', N'2017-10-23T20:49:08.6122864', N'2017-10-23T21:20:58.7073273', '41862277' ), 
( N'2017-10-23T17:20:58', N'2017-10-23T21:20:58.7073273', N'2017-10-23T21:21:34.426412', '41862277' ), 
( N'2017-10-23T17:21:34', N'2017-10-23T21:21:34.426412', N'2017-10-23T21:21:44.5514785', '41862277' )


DROP TABLE #temptable

Expected results: enter image description here

Upvotes: 0

Views: 133

Answers (1)

uzi
uzi

Reputation: 4146

This query returns your expected result for provided sample data. Check query inside CTE before running delete statement

with cte as (
    select 
        *, dr = dense_rank() over (partition by [key_column], cast([UPDATE_DATE] as date) order by cast([SysStart] as date))
    from 
        #temptable
)
delete from cte
where dr > 1

Upvotes: 2

Related Questions