Is it possible to create an empty table with a fixed number of rows (the number is defined in another table) and defined columns(col1 and col2) and then replace some of the rows in this empty table with the ones I get from a select?
I want the final structure to be like this :
col1 col2
----------------
empty empty
val11 val21
val12 val22
empty empty
val13 val23
empty empty
where I take val11, val21.... from another select. I want this table with empty values to be only local aka not create it anywhere else.
You can create a table with a variable number (say 5) of rows with empty fields; about the ordering, you can not use any internal ordering, so you need to add a field to hold the position of the row. For example:
SQL> create table tab_5(id number, col1 varchar2(50), col2 varchar2(50))
2 /
Table created.
SQL> insert into tab_5
2 select level, null, null
3 from dual
4 connect by level <= 5
5 /
5 rows created.
SQL> select * from tab_5 order by id;
ID COL1 COL2
---------- -------------------- --------------------
1
2
3
4
5
If you need to update some records in interlaced way, you can rely on your id
SQL> update tab_5
2 set col1 = 'something',
3 col2 = 'something else'
4 where mod(id, 2) = 0;
2 rows updated.
SQL> select * from tab_5 order by id;
ID COL1 COL2
---------- -------------------- --------------------
1
2 something something else
3
4 something something else
5
SQL>