SQL – Violation of UNIQUE KEY constraint

I am trying to find the solution for the below query:

There are declared tables @Country and @WHO (World Health Organization).

Insert all possibles countries from table @Country into table @WHO.

The code for the tables is:

DECLARE @Country TABLE 
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100),
    Capital NVARCHAR(100)
)

INSERT INTO @Country (Name, Capital) 
VALUES ('China', 'Beijing'), ('Japan', 'Tokyo'), 
       ('India', 'New Delhi'), ('South Korea', 'Seoul')

DECLARE @WHO TABLE
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    Country NVARCHAR(100) UNIQUE
)

INSERT INTO @WHO (Country) 
VALUES ('Japan'), ('South Korea')

The code that I started looks like this:

INSERT INTO @WHO (w.Country)
    SELECT DISTINCT c.Name
    FROM @Country c

SELECT w.Country 
FROM @WHO w 
ORDER BY w.Country

The final result should be a table that contains:

China
India
Japan
South Korea

I know this is probably an easy one but I cannot seem to find a solution.

What am I missing out from my code? 🙁

Answer

You can use not exists:

INSERT INTO @WHO (w.Country)
SELECT DISTINCT c.Name
FROM @Country c
WHERE NOT EXISTS (SELECT 1 FROM @WHO w WHERE w.Country = c.Name)

A (possibly overkill) alternative is the MERGE syntax:

MERGE INTO @WHO w
USING (SELECT DISTINCT Name FROM @Country) c
ON (c.Name = w.Country)
WHEN NOT MATCHED BY TARGET
THEN INSERT (Country) VALUES(c.Country)

Advertisement

Answer