CODEWITHSUNDEEP
sqloracle-database
Vasu
Vasu

Reputation: 22442

oracle select few columns from first table and insert (seq.nextval for one column + already selected columns) into second table

PRODUCT1 table:

CREATE TABLE PRODUCT1(PRD_ID INTEGER , PRD_NAME VARCHAR2(10) );

PRODUCT2 table:

CREATE TABLE PRODUCT2(PRD_ID INTEGER , PRD_NAME VARCHAR2(10) );

PRODUCT SEQUENCE:

CREATE SEQUENCE PRODUCT_SEQ  START WITH 100;

Assume that I have got few records (less than 100) already in PRODUCT1 and PRODUCT2.

I am trying below query by using the above tables and sequence:

INSERT INTO PRODUCT2 (PRODUCT_SEQ.NEXTVAL, PRD_NAME) SELECT PRD_NAME FROM PRODUCT1 ;

Error Report: SQL Error: ORA-00947: not enough values

Actually, I have gone through the below post to solve my problem:

select from one table, insert into another table oracle sql query

But unfortunately, that post has not helped me completely.

Please let me know if you need any more details from my side so that I can provide.

Upvotes: 1

Views: 2119

Answers (2)

Moudiz
Moudiz

Reputation: 7377

The sequence are numbers 100 , 102 , 103 ... so you insert those numbers in the column id

INSERT INTO PRODUCT2 
(PRD_ID, PRD_NAME)
 SELECT PRODUCT_SEQ.NEXTVAL , PRD_NAME FROM PRODUCT1 ;

Btw I advise you fix the structure of your column id , it is good practice if you make it ID number(4) because you have numbers starting with 100

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Your insert statement should have the column-names in the table. Then you should select the values from appropriate sequence and table.

INSERT INTO PRODUCT2 (PRD_ID, PRD_NAME) 
SELECT PRODUCT_SEQ.NEXTVAL, PRD_NAME FROM PRODUCT1

Upvotes: 1

Related Questions