SQL - insert rows preserving overall number of duplicate values

First I set up som tables for the testing:

create temp table Product(id  integer primary key generated always as identity, name text);
insert into Product (name) values ('one'),('two'),('three');
select * from Product;

create  temp table newdata(name text);
insert into newdata values ('one'),('two'),('two'),('two'),('four');
select * from newdata;

Then I use the analytical function "row_number()" which creates row numbers on the result. That is just a way of counting how many instances of each name is in your list

select name,row_number() over (partition by name order by name) rn from newdata;


+------+----+
| name | rn |
+------+----+
| four |  1 |
| one  |  1 |
| two  |  1 |
| two  |  2 |
| two  |  3 |
+------+----+

For product:

select name,row_number() over (partition by name order by name) rn from product


+-------+----+
| name  | rn |
+-------+----+
| one   |  1 |
| three |  1 |
| two   |  1 |
+-------+----+

Finding the differences between the two tells me which names I am missing:

select name,row_number() over (partition by name order by name) rn from newdata
except
select name,row_number() over (partition by name order by name) rn from product;


+------+----+
| name | rn |
+------+----+
| two  |  3 |
| four |  1 |
| two  |  2 |
+------+----+

Then I just need to insert the missing names:

insert into product (name)
select name from (
select name,row_number() over (partition by name order by name) rn from newdata
except
select name,row_number() over (partition by name order by name) rn from product
) a;
select * from product


+----+-------+
| id | name  |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
|  4 | two   |
|  5 | four  |
|  6 | two   |
+----+-------+