How to use insert into and join together-CodePudding

Can somebody help me with a question?

I am new to SQL and what I am trying to do is I have 2 tables t1 and t2. t1 is the main table and info from t2 is being inserted into t1 on daily basis.

I am trying to use insert and join together so that all the matching data in t1 and t2 won't be inserted into t1 again.

I want to insert only the unique items in table t2 into t1.

Table T1:

Date                Details
-------------------------------
01-02-2021 11.02    completed
01-02-2021 01.30    cancelled

Table T2:

Date                Details
-------------------------------
01-02-2021 11.02    completed
01-02-2021 01.30    cancelled
02-03-2021 03.30    running
02-03-2021 05.30    failed

CodePudding user response：

I suggest you use EXCEPT

Example:

INSERT INTO T1( [Date], Details )
SELECT [Date], Details
FROM T2
EXCEPT -- This is equivalent to subtracting T1 records from T2 records
SELECT [Date], Details
FROM T1

You can also do it with a LEFT JOIN:

INSERT INTO T1( [Date], Details )
SELECT [Date], Details
FROM T2
    -- Add all of the columns that you want to match one.
    LEFT JOIN T1 ON T2.[Date] = T1.[Date] AND T2.Details = T1.Details
-- This ensures that only records that did not match T1 are returned.
WHERE T1.[Date] IS NULL

Left Join method is useful when you want to return additional columns (in the SELECT column list) on top of the columns you are matching tables on.

Except method is better suited if you want to filter tables on all of their output columns.