SQL code takes duplicates in, even though I’ve specified it not to

I’ve written the following code:

insert into CDB2020.dbo.PartType
(
	Name,
	ComponentType_ID
)
select a.[Part Type], c.ComponentType_ID
from  AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.ComponentType as c on c.Name = a.[Component Type]
where [Part Type] is not null and [Part Type] not in (select [Name] from CDB2020.dbo.PartType);

The code takes in duplicates (e.g. Image of duplicated values), even though my last code line should have prevented it from doing so. The last line have worked with other parts of my code. Why does this method not work here, and how do I prevent duplicates?

Answer

You can try distinct:

select distinct a.[Part Type], c.ComponentType_ID

from  AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.ComponentType as c on c.Name = a.[Component Type]
where [Part Type] is not null and [Part Type] not in (select [Name] from CDB2020.dbo.PartType);

otherwise you may also try union.