Select from One Table, Insert into Another Table Oracle SQL Query

select from one table, insert into another table oracle sql query

From the oracle documentation, the below query explains it better

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

You can read this link

Your query would be as follows

//just the concept    
    INSERT INTO quotedb
    (COLUMN_NAMES) //seperated by comma
    SELECT COLUMN_NAMES FROM tickerdb,quotedb WHERE quotedb.ticker = tickerdb.ticker

Note: Make sure the columns in insert and select are in right position as per your requirement

Hope this helps!

Insert data into one table from another table avoiding duplicates

OK - from your description, I understand table t2 is currently empty, and you want to copy the rows where id is in (1, 2, 4) from table t1 to table t2.

Why your code fails:

You seem to believe that the condition is applied to the first row in t1, it passes so it is inserted into t2, then the condition is applied to the second row in t1 (using what is already inserted in t2), etc. - and you don't understand why there is any attempt to insert ALL the rows from t1 into t2. Why doesn't the third row fail the WHERE clause?

Good question! The reason is that operations are done on a SET basis. The WHERE condition uses table t2 AS IT WAS before the INSERT operation began. So for ALL rows, the WHERE clause compares to an empty table t2.

How to fix this... Decide which id you want to add when there are duplicate names. For example, one way to get the result you said you wanted is to select MIN(id) for each name. Moreover, you still want to check if the name exists in t2 already (since you may do this again in the future, when t2 is already partially populated).

insert into t2 ( id, name )
    select   min(id), name
    from     t1
    where    name not in (select name from t2)
    group by name
;

Select from multiple tables, insert into another table Oracle SQL query

If (fld_id1,fld_id2) are tbl_2.col1 and tbl_3.col1, so you you have to use JOIN

INSERT INTO tbl_1 (fld_id1,fld_id2)
SELECT t2.col1, t3.col1
FROM tbl_2 t2 
INNER JOIN tbl_3 t3 ON "JOIN CONDITION" 
WHERE "t2.someCondi"
AND "t3.someCondi"

So, if you can't use JOIN you can try with Cartesian Product:

INSERT INTO tbl_1 (fld_id1,fld_id2)
SELECT t2.col1, t3.col1
FROM tbl_2 t2, tbl_3 t3
WHERE "t2.someCondi"
AND "t3.someCondi"

Conditionally insert into another table if id exists in another table else insert into both both tables in oracle

The IF THEN ELSE logic is not needed for this case. Instead make use of the database built-in functionality. customerid should be your primary key, so if you try to insert and it already exists that will raise the DUP_VAL_ON_INDEX exception.

Check the following example:

-- create tables
create table customers (
    id                             number generated by default on null as identity 
                                   constraint customers_id_pk primary key,
    name                           varchar2(255 char)
)
;

create table orders (
    id                             number generated by default on null as identity 
                                   constraint orders_id_pk primary key,
    customer_id                    number
                                   constraint orders_customer_id_fk
                                   references customers on delete cascade,
    product                        varchar2(100 char)
)
;

BEGIN
  BEGIN
    insert INTO customers VALUES (2,'Capitol Industries Ltd');
  EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
    NULL; 
  END;
  insert into orders (customer_id,product) values (2,'a book');
END;  
/

run the above block a couple of times. Only the first time it will insert a customer.

Insert into ... values ( SELECT ... FROM ... )

Try:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

This is standard ANSI SQL and should work on any DBMS

It definitely works for:

Oracle
MS SQL Server
MySQL
Postgres
SQLite v3
Teradata
DB2
Sybase
Vertica
HSQLDB
H2
AWS RedShift
SAP HANA
Google Spanner

Select each row and insert into another table

The problem in in the position of the WHERE clause, which has to come after the list of joined table:

INSERT INTO T2 
 SELECT T1.*,  T3.DATETIME, T3.STATEID
 FROM T1 
 LEFT OUTER JOIN T3
   ON (T1.DOCID =  T3.DOCID)
WHERE T3.DATETIME BETWEEN '01-NOV-15' AND SYSDATE;

Oracle insert data from another table without column names

The only way is with some dynamic SQL, by relying on column names; for example, say you have the tables

CREATE TABLE Table1
(
    name                                    VARCHAR2(100),
    surname                                 VARCHAR2(100),
    age                                     NUMBER
);

CREATE TABLE Table2
(
    name                                    VARCHAR2(100),
    age                                     NUMBER,
    oneMoreColumn                           NUMBER,
    surname                                 VARCHAR2(100)
);

you can do:

declare
    vSQL    varchar2(1000);
    vCols   varchar2(1000);
begin    
    select listagg(tc1.column_name, ', ') within group (order by tc1.column_name)
    into vCols
    from user_tab_columns tc1
           inner join user_tab_columns tc2
             on(tc1.column_name = tc2.column_name)
    where tc1.table_name = 'TABLE1'
      and tc2.table_name = 'TABLE2';
    --
    vSQL := 'insert into table2( ' || vCols || ') select ' || vCols || ' from table1';
    --
    dbms_output.put_line(vSQL);
    --
    execute immediate vSQL;
end;

this will build and execute the statement:

insert into table2( AGE, NAME, SURNAME) select AGE, NAME, SURNAME from table1

Select data and insert into another table with multiple conditions in oracle

If only one of rj_span_id and rj_intracity_link_id can be not null, then coalesce might be what you're looking for:

insert into target (span_link_id, ne_length)
(
select coalesce(rj_span_id, rj_intracity_link_id)
       ROUND (SUM (NVL (calculated_length, 0) / 1000), 4) as NELENGTH
from ne.mv_span@facid147 
where rj_network_type IN ('NLD', 'City', 'NLD-City', 'ENT', 'FEEDER', 'FTTx')
  and rownum < 900000
  and  rj_span_id is not null 
  and rj_span_id <> '<Null>'
  and rj_intracity_link_id is not null 
  and rj_intracity_link_id <> 'NA' 
  and rj_intracity_link_id <> '-NA-'
group by rj_span_id, rj_intracity_link_id
);

Or, perhaps UNION of two almost same queries - one would insert rj_span_id, and another rj_intracity_link_id.

insert into target (span_link_id, ne_length)
(
select rj_span_id, 
       ROUND (SUM (NVL (calculated_length, 0) / 1000), 4) as NELENGTH
from ne.mv_span@facid147 
where rj_network_type IN ('NLD', 'City', 'NLD-City', 'ENT', 'FEEDER', 'FTTx')
  and rownum < 900000
  and rj_span_id is not null 
  and rj_span_id <> '<Null>'
  and rj_intracity_link_id is not null 
  and rj_intracity_link_id <> 'NA' 
  and rj_intracity_link_id <> '-NA-'
group by rj_span_id, rj_intracity_link_id
union all
select rj_intracity_link_id, 
       ROUND (SUM (NVL (calculated_length, 0) / 1000), 4) as NELENGTH
from ne.mv_span@facid147 
where rj_network_type IN ('NLD', 'City', 'NLD-City', 'ENT', 'FEEDER', 'FTTx')
  and rownum < 900000
  and rj_span_id is not null 
  and rj_span_id <> '<Null>'
  and rj_intracity_link_id is not null 
  and rj_intracity_link_id <> 'NA' 
  and rj_intracity_link_id <> '-NA-'
group by rj_span_id, rj_intracity_link_id
);

I don't know where is target table's ID column supposed to be populated, though.

How to insert the selected data columns from one oracle database table into another oracle database table in real time

I'd say that merge is what you need:

merge into mno.table_2 b  -- or, possibly, table_2@mno
  using table_1 a
  on (a.id = b.id)
  when matched then update set
    (b.col1 = a.col1,
     b.col2 = a.col2
    )
  when not matched then insert (col1, col2)
    values (a.col1, a.col2);

Note line #1:

if mno really is a "database" (and not just another user in the same database), then you'd have to use a database link (commented part of line #1)
if mno is just another user, then it should grant select, insert, update on its table to user xyz; otherwise, that operation won't work

Merge also means that you don't have to separately check whether that row exists in another table or not.

Shell scripting? Why? Keep everything within the database.

"Keep watch" can be done by

creating a database trigger on xyz.table_1 so that as soon as something changes, it fires and runs merge
or, schedule a job (using dbms_scheduler) which will periodically (for example, every hour, twice a day, ...) run merge

Inserting into an ORACLE table that has an auto increment sequence from another table?

You put the sequence to the wrong place; should've been in select, while insert into should contain the "target" column (let's presume its name is id):

INSERT INTO address (
    id,
    streetaddress,
    city,
    province,
    postalcode
)
    SELECT
        address.NEXTVAL,
        streetaddress,
        city,
        province,
        postalcode
    FROM
        import_employee

Error you got - if everything else was OK - means that you're inserting 4 values into 5 columns, and that's a mismatch.

Select from One Table, Insert into Another Table Oracle SQL Query