Search code examples
sql-servert-sqlcopyarchive

TSQL Copy New Contents to Archive Table Only

I have an ArchiveTable that I want to periodically copy any new records from OriginalTable. This is something I thought may work.

INSERT INTO OriginalTable 
    SELECT * 
    FROM ArchiveTable 
    WHERE NOT EXISTS (SELECT * 
                      FROM OriginalTable ot 
                      INNER JOIN ArchiveTable at ON ot.email = at.email)

Simply doing something like..

INSERT INTO ArchiveTable 
    SELECT * FROM OriginalTable

Of course, only works for the initial copy.

Solution

  • Your current query:

    INSERT INTO OriginalTable 
SELECT * FROM ArchiveTable 
WHERE NOT EXISTS 
(SELECT * FROM OriginalTable ot 
INNER JOIN ArchiveTable at 
ON ot.email = at.email)

    Uses an EXISTS subquery that isn't related to the outer query. So it's saying, "if no row exists in the original table that has the same email as any row in the archive table, then insert everything in the archive table into the Original table."

    Probably not what you want. You probably want to insert the specific rows that do not already exist in the original table. So you would want to correlate the subquery to the outer query:

    INSERT INTO OriginalTable  
SELECT * FROM ArchiveTable at
WHERE NOT EXISTS 
(SELECT * FROM OriginalTable ot 
WHERE ot.email = at.email)

    This query says, "insert into the original table, any rows in the archive table where I don't already have the Email in the Original table".