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!

Answers


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';

Need Your Help

C++ - Nested include - Avoiding 'include nested too deeply error'

c++ nested-includes

What is the best way of declaring my header files if I want to have the following connections in my C++ code, just so that I don't get the 'include nested too deeply error'?

DocumentListener is making issues in JTextField.setText()

java swing user-interface jcombobox jtextfield

Please have a look at the following 3 set of classes. Please note that only relevant code is shown, while rest of the majority is removed.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.