Oracle multiple inserts, different ids
I want to move some data from one table to another. I wrote a query that I think will work, but in the destination table, there is a column that contains an id. I want each row I insert to have a different and consecutive id. Here is a simplificated example:
Table1 Name Telephone Actie Peter 123456 Y Michael 111111 Y George 1234445 N Table2 Id Name Likes 1 Peter Books 2 Michael Books
The query I wrote:
insert all into table2 (name, likes) select all.name name, 'Books' likes, from (select o.name from table1 o where o.active='Y') all;
Is there any way of generating the consecutive ids and use the "insert all into" query? A sequence? If it can be made with a sequence... is there any way of doing it without a sequence?
Thanks and regards!
The proper way to generate id's in Oracle is by using a sequence.
create sequence seq_table2_id nocache;
Then upon inserting call seq_table2_id.nextval.
insert into table2 (id, name, likes) select seq_table2_id.nextval , o.name , 'Books' from table1 o where o.active = 'Y'
Personally I use a trigger to automatically call the sequence on inserts, but there are people who dislike using triggers.
You can use ROWNUM:
insert into table2 (id, name, likes) select ROWNUM, all.name name, 'Books' likes from (select o.name from table1 o where o.active='Y') all;
I'm not sure why you have the subquery (perhaps your real query is more complex?) You could write:
insert into table2 (id, name, likes) select ROWNUM, o.name, 'Books' likes from table1 o where o.active='Y';