How to append unique values from temp_tbl into original

I have a table that I'm trying to append unique values to. Every month I get list of user logins to import into this table. I would like to keep all the original values and just append the new and unique values onto the existing table. Both the table and the flatfile have a single column, with unique values, built like this:

_____
login
abcde001
abcde002
...
_____

I'm bulk ingesting the flat file into a temp table, with this:

IF OBJECT_ID('tempdb..#FLAT_FILE_TBL') IS NOT NULL 
    DROP TABLE #FLAT_FILE_TBL

CREATE TABLE #FLAT_FILE_TBL
(
    ntlogin2 nvarchar(15)
)

BULK INSERT #FLAT_FILE_TBL
FROM 'C:\ImportFiles\logins_Dec2021.csv'
WITH (FIELDTERMINATOR = ' ');

Is there a join that would give me the table with existing values new unique values appended? I'd rather not hard code a loop to evaluate it line by line.

Something like (pseudocode):

append unique {login} from temp_tbl into original_tbl

Hopefully it's an easy answer for someone out there.

Thanks!

CodePudding user response：

Poster on Reddit r/sql provided this answer, which I'm pursuing:

Merge statement?

It looks like using a merge statement will do exactly what I want. Thanks for those who already posted replies.

CodePudding user response：

You can check if a record exists using 'EXISTS' clause and insert if it doesn't exist in the target table. You can also use MERGE statement to achieve the same. Depending on what you want to do to the existing records in the target table, you can modify the Merge statement. Here since you only want to insert new records, you need to specify only what you want to do when a new record comes in. Here is an example

MERGE original_tbl T 
USING temp_tbl S 
  ON T.login = S.login
WHEN NOT MATCHED THEN
  INSERT (login)
  VALUES(S.login)

Another solution would be to left join the target table to the temp table and insert only when the record doesn't exist.

INSERT INTO original_tbl(login)
SELECT S.Login 
FROM  temp_tbl S 
LEFT JOIN original_tbl T
  ON S.Login = T.Login
WHERE T.Login IS NULL