Insert Select Statement in Oracle 11G

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.

Oracle 11g: INSERT SELECT with WITH statement

as mentioned here : https://forums.oracle.com/thread/696477
and here : insert statement with with clause invalid identifier oracle

the WITH clause is part of the select statemant , so just try to wrap it all with an INSERT statement as below :

INSERT INTO SOME_TABLE    
WITH seq AS
     (SELECT to_char(LEVEL - 1) p FROM dual CONNECT BY LEVEL <= 10)
    SELECT old_value,
           new_value
      FROM (SELECT to_char(t1.p) old_value,
                   to_char(t2.p) new_value
              FROM (SELECT row_number() over(ORDER BY p) rn,
                           p
                      FROM seq) t1,
                   (SELECT row_number() over(ORDER BY dbms_random.random) rn,
                           p
                      FROM seq) t2
             WHERE t1.rn = t2.rn);

INSERT INTO USING SELECT IN ORACLE

You don't need the VALUES clause if you are inserting from a SELECT.

INSERT INTO mytable
  SELECT mytable_seq.nextval,
         100,
         column1
    FROM anothertable
   WHERE column1 NOT IN (10,
                         20);

It is a best practice to list the columns in the destination table. This allows new ones to be added, that are NULLable, without disturbing existing DML.

INSERT INTO mytable
  (col1,
   col2,
   col3)
  SELECT mytable_seq.nextval,
         100,
         column1
    FROM anothertable
   WHERE column1 NOT IN (10,
                         20);

INSERT into statement not working in oracle when using INSERT INTO .. SELECT * FROM

Order of your insert clause column name and select clause column name should match.

Please match the positions of them as following:

INSERT INTO offc.vh_emp 
            (emp_id, 
             emp_name, 
             dob, 
             join_date, 
             leave_date, 
             Salary,  -- this
             dept_name, -- this
             address_one, 
             address_two,
             manager_name 
             ) 
SELECT e.emp_id       EMP_ID, 
       e.emp_name     EMP_NAME, 
       e.dob          DOB, 
       e.join_date    JOIN_DATE, 
       CASE 
         WHEN e.leave_date IS NULL THEN Trunc(sysdate) 
         ELSE e.leave_date 
       END            AS LEAVE_DATE, 
       e.salary / 100 SALARY, 
       d.dept_name    DEPT_NAME, 
       one.addr_name  ADDRESS_ONE, 
       two.addr_name  ADDRESS_TWO, 
       b.emp_name     AS MANAGER_NAME 
FROM   offc.employee e 
       LEFT JOIN offc.department d 
              ON e.dept_id = d.dept_id 
       LEFT JOIN offc.add_line_one one 
              ON e.line1 = one.addr_id_one 
       LEFT JOIN offc.add_line_two two 
              ON e.line2 = two.addr_id_two 
       LEFT OUTER JOIN (SELECT * 
                        FROM   offc.employee) b 
                    ON e.manager_id = b.emp_id; 

Cheers!!

Oracle 11g - Insert Into Select / Insert and update (dbfiddle example inside)

Use the following query in your INSERT INTO statement:

SELECT CODECLIENT, 174, 0, COUNT(*)
  FROM STATS_CLIENT_TEST
  where VALEURAXESTATISTIQUECLIENT='2021'
  GROUP BY CODECLIENT

db<>fiddle here

Oracle 11g insert into select from a table with duplicate rows

As far as I understood your problem, you just need to know the duplicate based on 2 columns. You can achieve it using analytical function as follows:

Select t.*, 
       row_number() Over(partition by main_id, eni order by company_name) as rnk
  From your_table t

INSERT SELECT with SELECT's column object

DECLARE
  objUser OBJ_USER ;
  tblUSERS NST_USERS;
BEGIN
  objUser := OBJ_USER('MARION', OBJ_USER_TYPE('USER', 3));
  tblUSERS := NST_USERS(objUser);

  INSERT INTO USERS(USERNAME, USER_TYPE)
    SELECT USERNAME, TREAT(USER_TYPE AS OBJ_USER_TYPE).USER_TYPE
     FROM
    TABLE(tblUSERS);

END;
/

Fiddle Demo

Oracle SQL: Use sequence in insert with Select Statement

Assuming that you want to group the data before you generate the key with the sequence, it sounds like you want something like

INSERT INTO HISTORICAL_CAR_STATS (
    HISTORICAL_CAR_STATS_ID, 
    YEAR,
    MONTH, 
    MAKE,
    MODEL,
    REGION,
    AVG_MSRP,
    CNT) 
SELECT MY_SEQ.nextval,
       year,
       month,
       make,
       model,
       region,
       avg_msrp,
       cnt
  FROM (SELECT '2010' year,
               '12' month,
               'ALL' make,
               'ALL' model,
               REGION,
               sum(AVG_MSRP*COUNT)/sum(COUNT) avg_msrp,
               sum(cnt) cnt
          FROM HISTORICAL_CAR_STATS
         WHERE YEAR = '2010' 
           AND MONTH = '12'
           AND MAKE != 'ALL' 
         GROUP BY REGION)

Best way to do multi-row insert in Oracle?

This works in Oracle:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

The thing to remember here is to use the from dual statement.

---

Related Topics