SQL - Violation of UNIQUE KEY constraint

Question

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

GMB · Accepted Answer · 2020-06-03 16:40:01Z

2

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)

edited Jun 3, 2020 at 16:40

answered Jun 3, 2020 at 16:37

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mădălina Jinga Over a year ago

It is working now, with only one amendment: (SELECT 1 FROM @WHO w WHERE w.Country= c.Name). Thank you very much!

user2076031 · Accepted Answer · 2021-03-20 11:09:18Z

1

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

answered Mar 20, 2021 at 11:09

user2076031

112 bronze badges

Collectives™ on Stack Overflow

SQL - Violation of UNIQUE KEY constraint

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related