I'm working on a table where I want to preserve old records. I want to distinguish between a current record and an old record by setting an 'active' flag to be 1 or 0.

Example: Table #a has all the current data.

Table #b has new data

Intention:

1. record 1 should not be modified in table #a (does not exist in #b)
2. record 2 should be updated as inactive, with new values inserted (#b provides new value)
3. record 3 should not be modified (no change to value)
4. record 4 should be inserted (does not exist in a)

Desired end result

This is my solution. My question is, is there a better way??

SELECT *
INTO #a
FROM (
SELECT 1 id, 1 value, 1 active
UNION ALL SELECT 2,2,1
UNION ALL SELECT 3,3,1
)t

SELECT *
INTO #b
FROM (
SELECT 2 id, 7 value
UNION ALL SELECT 3,3
UNION ALL SELECT 4,4
)t

SELECT * FROM #a

SELECT *
INTO #ut
FROM (
    SELECT id, value FROM #b
    EXCEPT
    SELECT id, value FROM #a
) ut

UPDATE a
    SET active = 0
FROM #a a
INNER JOIN #ut ut ON a.id = ut.id

INSERT INTO #a
SELECT ut.id, ut.value, 1 FROM #ut ut

SELECT * FROM #a

Note: I am also timestamping the records as well, but I wanted to keep this example as simple as possible. Unique key would be combination of id and createdon timestamp

Edit: I should add some of these tables I'm working with have millions of rows, so performance is important.

CodePudding user response：

-- set active to 0 when a new record will be added by a matched record
merge into #a a using #b b on a.id = b.id
  when matched and a.value != b.value then update set active = 0;
-- create new record for changed values and new items
merge into #a a using #b b on a.id = b.id and a.value = b.value
  when not matched then insert(id, value, active) values(b.id, b.value, 1);