SQL insert into using Union should add only distinct values

Question

So I have this temp table that has structure like:

col1    col2    col3    col3
intID1  intID2  intID3  bitAdd

I am doing a union of the values of this temp table with a select query and storing it into the same temp table.The thing is col3 is not part of the union query I will need it later on to update the table.

So I am doing like so:

Insert into  #temptable
(
intID1,
intID2,
intID3
)
select intID1,intID2,intID3
From
#temptable

UNION

select intID1,intID2,intID3
From
Table A

Issue is that I want only the rows that are not already existing in the temp table to be added.Doing it this way will add a duplicate of the already existing row(since union will return one row)How do I insert only those rows not existing in the current temp table in my union query?

Answer 1

Use MERGE:

MERGE INTO #temptable tmp
USING (select intID1,intID2,intID3 From Table A) t
ON (tmp.intID1 = t.intID1 and tmp.intID2 = t.intID2 and tmp.intID3 = t.intID3)
WHEN NOT MATCHED THEN
INSERT (intID1,intID2,intID3)
VALUES (t.intID1,t.intID2,t.intID3)

Answer 2

Nice and simple with EXCEPT

INSERT INTO  #temptable (intID1, intID2, intID3)
SELECT intID1,intID2,intID3 FROM TableA
EXCEPT
SELECT intID1,intID2,intID3 FROM #temptable