CODEWITHSUNDEEP
sqlsql-serverselectsql-insertunique-constraint
Mădălina Jinga
Mădălina Jinga

Reputation: 55

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? :(

Upvotes: 0

Views: 578

Answers (2)

user2076031
user2076031

Reputation: 11

The expected query of the task would be

INSERT INTO @WHO (Country)
SELECT
    c.Name
FROM @Country c
LEFT JOIN @WHO w ON w.Country = c.Name
WHERE w.Id IS NULL

though I like the 'not exists' version as more universal

Upvotes: 1

GMB
GMB

Reputation: 222462

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)

Upvotes: 2

Related Questions