CODEWITHSUNDEEP
sqlsql-server
sukesh
sukesh

Reputation: 2423

SQL - How to avoid inserting duplicate rows from another table while matching multiple columns

Using Microsoft SSMS.

There are two tables

Table1

ID | SessionId | Handle  | StartDate          |
---------------------------------
1  | abcd      | handle1 | 2020-08-01 00:00:00|
2  | abcd      | handle2 | 2020-08-01 00:00:00|

TempTable

SessionId | Handle  | StartDate          |
---------------------------------
 abcd     | handle1 | 2020-08-01 00:00:00|
 abcd     | handle2 | 2020-08-01 00:00:00|
 abcd     | handle3 | 2020-08-01 00:00:00|  --only this record has to be inserted to Table1

I want to insert only the last row in TempTable to Table1, because the other 2 records are present already.

A row is a duplicate only when all the column values in TempTable match with a record on Table1.

I'm not sure how to use NOT IN or INTERSECT or any other alternate, as there are multiple column values to be matched.

UPDATE:

Following @llyes suggestion, helped me resolve this.

INSERT INTO table1 (SessionId, Handle, StartDate)
SELECT SessionId, Handle, StartDate
FROM TempTable t2
WHERE NOT EXISTS(
    SELECT 1
    FROM Table1 t1
    WHERE t1.SessionId= t2.SessionId
    AND t1.Handle = t2.Handle   
    AND t1.StartDate = t2.StartDate 
);

Upvotes: 1

Views: 55

Answers (1)

Ilyes
Ilyes

Reputation: 14928

You could use EXCEPT as the following

SELECT SessionId, Handle, StartDate
FROM
(
  VALUES
  ('abcd', 'handle1', '2020-08-01 00:00:00'),
  ('abcd', 'handle2', '2020-08-01 00:00:00'),
  ('abcd', 'handle3', '2020-08-01 00:00:00')
) TempTable(SessionId, Handle, StartDate)
EXCEPT
SELECT SessionId, Handle, StartDate
FROM
(
  VALUES
  (1, 'abcd', 'handle1', '2020-08-01 00:00:00'),
  (2, 'abcd', 'handle2', '2020-08-01 00:00:00')
) YourTable(ID, SessionId, Handle, StartDate);

Demo

You could also use NOT EXISTS() as

SELECT *
FROM TempTable TT
WHERE NOT EXISTS(
                  SELECT 1
                  FROM YourTable YT
                  WHERE YT.SessionId = TT.SessionId
                        AND YT.Handle = TT.Handle 
                        AND YT.StartDate = TT.StartDate
                );

Upvotes: 3

Related Questions