0

I have some table and trying load data into it by selecting row from another table like below.

insert into emp2(eid,ename,sal,my_seq_num) select eid,ename,sal from emp1;

When I executed the above query I am getting the error 'not enough values' because I selected only three rows but insert contains 4 columns .So my question is how can Insert a sequence number automatically (like we write SYSDATE to write into date column) .

APC
  • 144,005
  • 19
  • 170
  • 281
prasad
  • 339
  • 8
  • 23
  • look for this question: http://stackoverflow.com/questions/10613846/create-table-with-sequence-nextval-in-oracle – HAYMbl4 Sep 25 '15 at 06:53

3 Answers3

0
insert into emp2(eid,ename,sal,my_seq_num) 
select eid,ename,sal,sequence_name.nextval 
from emp1;
Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49
0

insert into emp2(eid,ename,sal,my_seq_num) select eid,ename,sal,mention your sequence name from emp1;

For example :- insert into emp2(eid,ename,sal,my_seq_num) select eid,ename,sal,my_seq_num.nextval from emp1;

Bala S
  • 495
  • 1
  • 6
  • 17
0

First, you need create a SEQUENCE object

CREATE SEQUENCE sequence_name
  MINVALUE value
  MAXVALUE value
  START WITH value
  INCREMENT BY value
  CACHE value;

In your case:

CREATE SEQUENCE my_sequence
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

Then, you can get the next number in the sequence with nextval, like:

SELECT my_sequence.NEXTVAL FROM DUAL

In your INSERT:

insert into emp2(eid,ename,sal,my_seq_num) 
select eid,ename,sal,my_sequence.NEXTVAL from emp1;

Good Luck!

David Isla
  • 619
  • 7
  • 19
  • Hi @David Isla ,Thank you so much for ur reply.And I have two more doubts here.1) Does Create sequence work in every database (like MySQL,SQL,SYBASE etc ..)?. 2)How can I Create this sequence from from Java program? – prasad Sep 25 '15 at 08:57
  • `SEQUENCE` is an Oracle Object. You have other possibilities in others rdbms, like defining an [Auto_increment](https://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html) columns in MySQL, etc. About creation from Java, think in `SEQUENCE` similar than `TABLES`, define it in design time. – David Isla Sep 25 '15 at 09:40
  • SQL Server has `SEQUENCE` object too. Read [here](https://msdn.microsoft.com/en-us/library/ff878058.aspx) – David Isla Sep 25 '15 at 09:45