T-SQL: INSERT IF NOT EXISTS

Question

I have two tables:

people

| peopleID | Lastname | Firstname |
| -------- | -------- | --------- |
| 1        | Smith    | Marc      |
| 2        | Doe      | John      |
| 3        | Davidson | Terry     |
| 4        | Meyer    | Todd      |
| 5        | Richards | Abe       |

customers

| customerID | Lastname | Company             |
| ---------- | -------- | ------------------- |
| 1          | Davidson | Wonderproducts Inc. |
| 2          | Meyer    | Banana Inc.         |

Now I want to insert all elements of the table people to the table customers, except the ones, where the lastname equals the lastname in customers.

So at the end customers should look like this:

| customerID | Lastname | Company             |
| ---------- | -------- | ------------------- |
| 1          | Davidson | Wonderproducts Inc. |
| 2          | Meyer    | Banana Inc.         |
| 3          | Smith    |                     |
| 4          | Doe      |                     |
| 5          | Richards |                     |

I already tried around this:

IF NOT EXISTS
  (SELECT 1 FROM customers WHERE Lastname = (SELECT Lastname FROM people))
INSERT INTO customers (Lastname) VALUES (SELECT Lastname FROM people)

Why do you need to use an `EXISTS` here? A `INSERT INTO...SELECT...FROM...WHERE` (emphasis on the `WHERE`) seems far simpler. An `EXISTS` would stop any rows being inserted, not just the ones you don't want. — Thom A, Nov 06 '20 at 10:55
I'm not sure, however, we need another duplicate answer of "how to `INSERT` data from one table to another" though, @TimBiegeleisen ;) — Thom A, Nov 06 '20 at 11:00
Does this answer your question? [Insert into ... values ( SELECT ... FROM ... )](https://stackoverflow.com/questions/25969/insert-into-values-select-from) — Thom A, Nov 06 '20 at 11:00
I've always wondered about the duplicate threshold. Is it an _exact_ dupe? The data are different. Maybe you're right... — Tim Biegeleisen, Nov 06 '20 at 11:00
Personally, @TimBiegeleisen , I don't think that the data or design need to be identical, but the idea. For example, at the end of the day asking "how to create a delimited list in SQL Server" is the same regardless of if your table is called `Sales`, `Customers` or `Schools` and the column is called `Product`,`CustomerName` or `HeadMaster`; use `STRING_AGG` or `FOR XML PATH`. — Thom A, Nov 06 '20 at 11:03
As a SQL beginner: I totally understand that there are several questions similar to this. But in this specific case I do not know how to skip those entries, I described in the post. How would I be able to exclude (partly) existing rows using `INSERT INTO...SELECT...FROM...WHERE`? I cannot do something like `INSERT INTO Customers (Lastname) VALUES (SELECT Lastname FROM People WHERE Lastname != (SELECT Lastname FROM Customers))` — Gardinero, Nov 06 '20 at 13:37

score 1 · Accepted Answer · answered Nov 06 '20 at 15:11

1

Try with this

INSERT INTO customers (Lastname)
SELECT P.Lastname
FROM people P
    LEFT JOIN customers C ON C.Lastname = P.Lastname
WHERE C.customerID IS NULL

answered Nov 06 '20 at 15:11

davide-pi

310
1
9

T-SQL: INSERT IF NOT EXISTS

1 Answers1