Inserting data into a table(mutliple columns) which has primary key from another data which has data except primary key

Question

I have a table that has 3 columns ID(Primary Key), Name, City.

I need to import data from another table that has only Name and City.

I can write insert into table 1(Name, City) select Name, City from table2.

But then I need ID in table 1 which needs to be inserted using a sequence.

I tried this:

insert into table1(ID, Name,City) values(seq.nextval, select distinct name, city from table2). But I am receiving an error saying an insufficient number of values.

I am trying it in SQL Oracle. Can someone please help me with this?

Answer 1

You are mixing the insert ... values and insert ... select syntax.

You edited your question to include distinct, implying you have duplicate name/city pairs that you want to suppress; but neither version gets the error you reported. If you don't have duplicates then you can just do:

insert into table1(ID, Name,City)
select seq.nextval, name, city from table2;

If you do have duplicates then you can't just add the distinct keyword, but you can use a subquery:

insert into table1 (id, name, city)
select seq.nextval, name, city
from (
  select distinct name, city
  from table2
);

db<>fiddle

You could also set the ID via a trigger. If you we're on a recent version you could use an identity column instead - but you tagged the question with Oracle 11g, where those are not available.