Oracle Insert Select with Order By

INSERT with ORDER on Oracle

You can not reliably control in what order Oracle retrieve the row of a table without an ORDER BY.

Furthermore, without the /*+APPEND*/ hint, Oracle will store the rows physically in a heap table where there is room, which may not be at the end of the table ! You may think Oracle inserts them sequentially but any DML or concurrent activity (2+ sessions inserting) might produce a different physical organization.

You could use an INDEX ORGANIZED table to store the rows in the order of the PK. Most simple queries thereafter on that table will produce a sorted set of rows. This would not however guarantee that oracle will select the rows in that order if you don't specify an ORDER BY (depending on the query and the access path, the rows may come in any order).

You could also use a view with an order by, this is probably your best bet if you can't touch the application (rename the table, create a view with the name of the table, let the application think it queries the table). I don't know if it is feasible in your case.

INSERT SELECT statement in Oracle 11G

Your query should be:

insert into table1 (col1, col2) 
select t1.col1, t2.col2 
from oldtable1 t1, oldtable2 t2

I.e. without the VALUES part.

How does Oracle Insert Into work when order of values is not defined?

The order of columns in a table in Oracle IS defined. Take a look at the ALL_TAB_COLUMNS view - there's a COLUMN_ID column which defines the order of columns within the table. If a field list is not given in a SELECT (i.e. SELECT * FROM MY_TABLE) the columns from MY_TABLE will be returned in ALL_TAB_COLUMNS.COLUMN_ID order. This is also the same way columns are ordered in a %ROWTYPE variable, and it's the way that an INSERT which doesn't have a field list specified expects fields to be ordered.

Oracle: Insert Rows from Select with New Sequence IDs

First create a query that will display the rows you want:

SELECT
    Id,
    col1, col2, col3, ....., colN
FROM table
WHERE /* the condition which selects rows you want */
   id >= 6 AND id <= 10

then replace ID column with TABLE_SEQUENCE.NEXTVAL and prepend the whole query with INSTERT statement:

INSERT INTO table( Id, col1, col2, col3, ....., colN )
SELECT
    TABLE_SEQUENCE.NEXTVAL,
    col1, col2, col3, ....., colN
FROM table
WHERE /* the condition which selects rows you want */
   id >= 6 AND id <= 10

insert into... select ... with subquery or without column order

No, you can't use a subquery to generate the column list as part of an SQL statement.

You can generate the full statement from the data dictionary:

select 'insert into cl ("'
  || listagg(column_name, '","') within group (order by column_id)
  || '") select "'
  || listagg(column_name, '","') within group (order by column_id)
  || '" from clt'
from user_tab_columns where table_name = 'CLT';

and then either copy and paste that, or use dynamic SQL from an anonymous block:

declare
  stmt varchar2(4000);
begin
  select 'insert into cl ("'
    || listagg(column_name, '","') within group (order by column_id)
    || '") select "'
    || listagg(column_name, '","') within group (order by column_id)
    || '" from clt'
  into stmt
  from user_tab_columns where table_name = 'CLT';

  dbms_output.put_line(stmt); -- to check and debug
  execute immediate stmt;
end;
/

With a couple of dummy tables:

create table clt (col1 number, col2 date, col3 varchar2(10));
create table cl (col3 varchar2(10), col1 number, col2 date);

insert into clt (col1, col2, col3) values (42, date '2018-07-12', 'Test');

insert into cl
select * from clt;

SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

running that block gives:

insert into cl ("COL1","COL2","COL3") select "COL1","COL2","COL3" from clt

PL/SQL procedure successfully completed.

select * from cl;

COL3             COL1 COL2      
---------- ---------- ----------
Test               42 2018-07-12

You could also turn that anonymous block into a procedure that takes two table names if this is something you're likely to want to do often (you said it needed to be reusable, but that could mean for the same tables, and could just be a block in a script).

You could also go further and only include columns that appear in both tables, or verify data types match exactly; though that's a bit more work and may well not be necessary.

Oracle SQL: INSERT with SELECT

This would look like:

insert into book (book_id, category_id, book_name, buy_price, sell_price)
    select ?, c.category_id, ?, ?, ?
    from category c
    where c.category_name = ?;

The ? are placeholders for your constant values. Note that there are no single quotes around the column names for the insert.

Oracle - how to insert if not exists?

Use an INSERT .. SELECT statement with a PARTITIONed outer join:

INSERT INTO stats_client_test (
  codeclient, codeaxestat, codeelementstat, valeuraxestatistiqueclient
)
  SELECT cc.codeclient,
         s.codeaxestat,
         s.codeelementstat,
         'UNKNOWN'
  FROM   (SELECT DISTINCT codeclient FROM stats_client_test) cc
         LEFT OUTER JOIN stats_client_test s
         PARTITION BY (s.codeaxestat, s.codeelementstat)
         ON (s.codeclient = cc.codeclient)
  WHERE  s.rowid IS NULL;

or a MERGE statement:

MERGE INTO stats_client_test dst
USING (
  SELECT cc.codeclient,
         s.codeaxestat,
         s.codeelementstat,
         s.ROWID AS rid
  FROM   (SELECT DISTINCT codeclient FROM stats_client_test) cc
         LEFT OUTER JOIN stats_client_test s
         PARTITION BY (s.codeaxestat, s.codeelementstat)
         ON (s.codeclient = cc.codeclient)
) src
ON (dst.ROWID = src.rid)
WHEN NOT MATCHED THEN
  INSERT (codeclient, codeaxestat, codeelementstat, valeuraxestatistiqueclient)
  VALUES (src.codeclient, src.codeaxestat, src.codeelementstat, 'UNKNOWN');

db<>fiddle here

---

Related Topics