INSERT INTO a table comparing two other tables using Not EXISTS

I have 3 tables:

• Server_db_relation
• DatabaseList
• DatabaseList_Archive

The goal is to compare table Server_db_relation and DatabaseList based on the values in column ServerName and DatabaseName. If the values do not exists in the table Server_db_relation, but do exist in DatabaseList, then it should insert the entire row from DatabaseList into DataList_Archive (those two tables are identical in structure).

In addition, if possible ones the row has been inserted to DatabaseList_Archive then delete the record from DatabaseList.

INSERT INTO DatabaseList_Archieve (Column1, Column2...)
    SELECT * 
    FROM DatabaseList
    WHERE NOT EXISTS (SELECT 1 
                      FROM DabaseList, Server_db_relation 
                      WHERE DatabaseList.ServerName = Server_db_relation.ServerName 
                        AND DatabaseList.DatabaseName = Server_db_relatio.[Database])

I do not get any syntax errors but no rows are affected. I have added 2 test rows that I know exist in DatabaseList and not in Server_db_relation.

Answer

You need correlate the sub-query :

INSERT INTO DatabaseList_Archieve (Column1, Column2...)
    SELECT dbl.col1, dbl.col2, . .  
    FROM DatabaseList dbl
    WHERE NOT EXISTS(SELECT 1 
                     FROM Server_db_relation sdb 
                     WHERE dbl.ServerName = sdb.ServerName AND 
                           dbl.DatabaseName = sdb.[Database]
                    );

Note : I have declared the table alias to make the query easy to ready & write column name or table name (where it comes from).