Multi column exist statement

Question

I am trying to insert data to a table from another where data is not already exists

The table I am inserting the data into

CREATE TABLE #T(Name VARCHAR(10),Unit INT, Id INT)
INSERT INTO #T
    VALUES('AAA',10,100),('AAB',11,102),('AAC',12,130)

The table I am selecting the data from

CREATE TABLE #T1(Name VARCHAR(10),TypeId INT,Unit INT, Id INT)
INSERT INTO #T1
VALUES('AAA',3,10,100),('AAA',3,10,106)

In this case I want to select ('AAA',3,10,106) from #T1 because AAA,106 combination not exists in #T

Basically what I want is to populate unique Name and Id combination

I tried below which doesn't seems to work

SELECT *
FROM #T1
WHERE NOT EXISTS(SELECT * FROM #T)

Answer 1

You have to somehow correlate the two tables:

SELECT *
FROM #T1 
WHERE NOT EXISTS(SELECT * 
                 FROM #T
                 WHERE #T1.Name = #T.Name AND #T1.ID = #T.ID)

The above query essentially says: get me those records of table #T1 which do not have a related record in #T having the same Name and ID values.