Identify new values in Table A and insert them in Table B-CodePudding

I have two tables: Table A and Table B. B is suppose to contain all Ids from A, and then when I run a query each time I need to check for newly added Ids in A that are not already contained in B, and insert them to B. A is being updated daily with each row having a unique Id.

I only need to record 4 columns from A into B (Id, Region, Department, EntryDate), with EntryDate being the date when Id was added into B. B serves as sort of historic record of when Ids and when they first appeared in A.

What would be the query for inserting this new data from A to B?

CodePudding user response：

To identify new value you should add new column to table A named IsTransfered with default value 0.

Alter Table A Add IsTransfered BIT Default(0)

Then create a job with this query to transfer new value.

Begin Transaction 
Begin Try
DECLARE @InsertedIDs Table (ID Int)

Insert B (Id, Region, Department, EntryDate)
Select Id, Region, Department, EntryDate
Output inserted.Id INTO @InsertedIDs 
From A
Where IsTransfered  = 0

Update A
SET IsTransfered = 1
FROM @InsertedIDs I
INNER JOIN A ON I.ID = A.Id

COMMIT
END TRY
BEGIN CATCH
    ROLLBACK  
END CATCH

As you can see in query it updates IsTransfered after transferring new records. And it must be within a transaction.

For more performance gain create a filtered index:

CREATE NONCLUSTERED INDEX IX_FilterdByIsTransfered
    ON A (ID)  
    WHERE IsTransfered = 0;

CodePudding user response：

Would you be able to create a view? this way no job needs to be run and they will always be in sync.

create view b as

Select Id, Region, Department, min(EntryDate) as EntryDate
from a
group by Id, Region, Department

CodePudding user response：

use EXCEPT Operator to get new IDs in table A and then use where .. IN with select to Insert data into table B as follows.

INSERT INTO B (Id, Region, Department, EntryDate)
---select new IDs and it's associated values from table a
SELECT Id,
       Region,
       Department,
       EntryDate
FROM   A
WHERE  id IN(
             SELECT DISTINCT id --get new IDs in table a
             FROM   A
             EXCEPT
             SELECT DISTINCT id
             FROM   B)