Process staging table by getting unique rows only (incremential) I had simple problem with etl on staging table that was answered here:

Process staging table by getting unique rows only

Now after apllying solution I spotted that will be unusable in production enviroment due to amount of data (around 1 mln rows daily) and that will be longering execution of etl statement every day by 1 minute at least on my dba infrastructure, then it will be unusable after few months.

Also staging table can't be locked in any time for INSERT operations.

How to do that etl in incremential fashion? like:


open transaction
take rows from staging
process that new rows (eliminate rows that exists in final table, not agaist staging table, like in non-incremential solution answered in thread linked above)
insert new rows into final table
delete rows from staging table taken at point 2.
commit transaction


Problematic is only 3 point, can anyone help?
OK, let's look at a simple change to the code snippet from the previous post.

-- Create indexes to support the unique merge of the two tables.
CREATE TABLE StagingTable ([Key] INT, A INT, B INT); 
  CREATE INDEX Staging_MergeHelp ON StagingTable (A, B);
CREATE TABLE FinalTable ([Key] INT IDENTITY, A INT, B INT);
  CREATE INDEX Final_MergeHelp ON FinalTable (A, B);
-- 
--  A first run of your job to insert unique values
--
INSERT INTO STAGINGTABLE VALUES( 1,1,1)
INSERT INTO STAGINGTABLE VALUES( 2,1,2)
INSERT INTO STAGINGTABLE VALUES( 3,1,1)
INSERT INTO STAGINGTABLE VALUES( 4,1,2)
INSERT INTO STAGINGTABLE VALUES( 5,2,1)
--
INSERT INTO FinalTable (S.A, S.B)
SELECT S.A, S.B
FROM StagingTable S
   LEFT OUTER JOIN FinalTable F
      ON S.A = F.A AND S.B = F.B
WHERE F.A IS NULL -- i.e. does not exist in FinalTable
GROUP BY S.A, S.B
--
--  First Answer
SELECT * FROM FinalTable
-- 
--  A second run of your job to insert NEW unique values
--
INSERT INTO STAGINGTABLE VALUES( 6,1,1)
INSERT INTO STAGINGTABLE VALUES( 7,1,2)
INSERT INTO STAGINGTABLE VALUES( 8,3,1)
INSERT INTO STAGINGTABLE VALUES( 9,1,2)
INSERT INTO STAGINGTABLE VALUES( 10,2,4)
--
INSERT INTO FinalTable (S.A, S.B)
SELECT S.A, S.B
FROM StagingTable S
   LEFT OUTER JOIN FinalTable F
      ON S.A = F.A AND S.B = F.B
WHERE F.A IS NULL -- i.e. does not exist in FinalTable
GROUP BY S.A, S.B
--   
-- Cumulative answer after including newer values  
SELECT * FROM FinalTable
-- 
DROP TABLE FinalTable
DROP TABLE StagingTable


If this FinalTable gets really big, then there will be some serious overhead.  That is why this time I created indexes on both StagingTable and FinalTable for columns A and B.  This will help the joins run more efficiently.  

However, there are also more powerful tools, such as SSIS which could be a big help as the job gets bigger.  The code above is just a simple picture of how to construct the logic.