Ice Ice - 4 months ago 7
SQL Question

How to insert data to table from differences between this table and other one, using genereted sequence id?

I have two tables and I try to insert rows from

SECOND_TABLE
to
FIRST_TABLE
.

I create two tables:

CREATE TABLE FIRST_TABLE(
F_ID NUMBER(10) not null,
F_NAME VARCHAR(8 BYTE) not null,
F_DESCRIPTION NVARCHAR2(1000) not null
);


CREATE TABLE SECOND_TABLE(
S_ID NUMBER(10) not null,
S_NAME VARCHAR(8 BYTE) not null,
S_DESCRIPTION NVARCHAR2(1000) not null
);


I found differences between
SECOND_TABLE
and
FIRST_TABLE
:

select S_NAME,S_DESCRIPTION from SECOND_TABLE minus select F_NAME,F_DESCRIPTION from FIRST_TABLE;


I create a statement:

insert into FIRST_TABLE F_ID,F_NAME,F_DESCRIPTION (select * from SECOND_TABLE where not exists (select * from FIRST_TABLE where SECOND_TABLE.S_NAME = FIRST_TABLE.F_NAME));


but this statement copy
F_ID
, I don't have idea how to change this statement to not copy
F_ID
, but generate it from sequence
CSS_F
. It should looks like:

insert into FIRST_TABLE (F_ID) values (CSS_F.nextval);


Could you give me an advice how to add inserting genereted id to this statement?

vkp vkp
Answer

You can select the name and description columns from the second_table and use the sequence for inserting the F_ID column.

insert into FIRST_TABLE (F_ID,F_NAME,F_DESCRIPTION)
select CSS_F.nextval, S_NAME, S_DESCRIPTION
from SECOND_TABLE S
where not exists (select * from FIRST_TABLE where S.S_NAME = F_NAME)
Comments