SQL & PL/SQL :: Insert Into Tab1 Rows Which Are In Tab2 But Not In Tab1

Oct 21, 2010

I have 2 tables:

tab1

ID seq
1 1

tab 2

ID seq
1 1
1 2

I want to insert into tab1 the rows which are in tab2 but not in tab1..so in this example i end up with:

tab1

ID seq
1 1
1 2

View 20 Replies


ADVERTISEMENT

PL/SQL :: Create Trigger If Any Insertion In TAB1 Then Records Get Inserted Into TAB2?

Oct 30, 2012

i have requirement like this i don't know abt trigger

create trigger with the below:
Tables: TAB1 TAB2
Create a trigger, if any insertion in TAB1 then records should get inserted into TAB2
create a trigger, if any updation in TAB1 then record should get inserted into TAB2
Create a trigger, if any deletion in TAB1 then record should get inserted into TAB2

TAB1 and TAB2 can be any table.

View 2 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related

SQL & PL/SQL :: How To Insert Parametric Rows

Oct 14, 2010

I have to insert many computed rows (not coming from a select...where...) into a target table.

I wrote a code like the following example; it works but it seems to me not efficient:

WHILE l_data < l_dt_end
LOOP
l_value := MY_FUNCTION( l_data );
l_color := MY_COLOR( l_data );
INSERT INTO MY_TARGET
( value, color )
VALUES( l_value, l_color );
l_data := l_data + 1;
END LOOP;
COMMIT;
EXCEPTION
...

ROLLBACK;Which is the best way to write this?

View 9 Replies View Related

SQL & PL/SQL :: Unable To Insert Rows

Aug 4, 2010

In oracle 11i, unable to insert rows using insert statement. unable to retrieve data using select statement.

View 5 Replies View Related

PL/SQL :: Trigger For To Insert Other Rows

May 2, 2013

I must to build triggers that insert other two rows when the user insert the first record, but the First record must to change Seq to 2 (was 1) then in trigger to insert other record with seq equal 1 and more other record with seq equal 800, I tried some ways , but return error

First insert the user from application

INSERT INTO ARCTB_ACAO_IMEDIATA ( CD_ARC,  CD_TIPO_ACAO,  CD_ACAO_IMEDIATA,  DS_ACAO,  CD_RESPONSAVEL,  DT_ORIGINAL,  DT_ABERTURA,  NR_PRAZO,DT_PREVISTA, DS_HISTORICO ) VALUES (9732, 0,1, 'TESTE','ucrwilma', To_date('02/05/2013','DD/MM/YYYY'), To_date('02/05/2013','DD/MM/YYYY'), 1, To_date('02/05/2013','DD/MM/YYYY'), '');I create triggers
CREATE OR REPLACE TRIGGER ARCTR_ACAO_IMEDIATA_I
BEFORE INSERT

[Code]....

END ARCTR_ACAO_IMEDIATA_IUreturn me error
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "CLIBGF.ARCTR_ACAO_IMEDIATA_IU", line 221
ORA-04088: error during execution of trigger 'CLIBGF.ARCTR_ACAO_IMEDIATA_IU'
ORA-06512: at line 7using 9.2.08

View 2 Replies View Related

SQL & PL/SQL :: Unable To Insert Rows Into Table

Jun 10, 2011

I am unable to insert the rows into table after creation of trigger because Mutating error was getting.

SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NAME VARCHAR2(10)
EMP_NO VARCHAR2(10)

SQL> select * from test;

EMP_NAME EMP_NO
---------- ----------
ORACLE PC729
JAVA PC047
C PC0729

SQL> create or replace trigger trig_test
2 after insert on test
3 for each row
4 declare
5 ename varchar2(10);
6 eno varchar2(10);
7 begin
8 select emp_name,emp_no into ename,eno from test
9 where emp_no ='1';
10 insert into test2(emp_name,emp_no) values(
11 ename,eno);
12 end;
13 /

Trigger created.

SQL> insert into test values ('PRO','1');
insert into test values ('PRO','1')
*
ERROR at line 1:
ORA-04091: table APPS.TEST is mutating, trigger/function may not see it
ORA-06512: at "APPS.TRIG_TEST", line 5
ORA-04088: error during execution of trigger 'APPS.TRIG_TEST'
SQL> spool off;

View 5 Replies View Related

SQL & PL/SQL :: To Insert The Rows Into A Table Target

Oct 3, 2011

I have a table named SOURCE with about 1.000.000 rows looking like this

date varchar2 varchar2 varchar2
DATE ID1 ID2 AMOUNT
---------- ----------- --------- --------
2010-07-03 1403 1403 1500
2010-07-13 2015438 etc 188608 6074
2010-08-28 1151927 410,4222 1750
2010-08-28 13622012 41026 178.99
2010-08-28 1600246 John 65

I want to insert the rows into a table TARGET

date integer integer number
DATE ID1 ID2 AMOUNT

There are about 10.000 rows where ID1, ID2 and/or AMOUNT contains characters. These rows I don't want to insert as the columns in the target table are INTEGER. I simply want to discard these.

View 3 Replies View Related

Insert Rows Based On Text Values?

Mar 22, 2007

I am an Oracle beginner and I am having some trouble with the following insert query.

I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.

INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338);
INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);

The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"

View 1 Replies View Related

Insert Separated Data As Individual Rows

Sep 3, 2008

I have a table with three columns X, Y and Z.The data in Column z is of the type 20/1425SE, 13/1235NW.Is there a way to split the data entries where Z LIKE '%/% and insert them as two separate rows.

I don't want to have any entries with '/'. Can these be deleted along with splitting the data entries?

View 3 Replies View Related

SQL & PL/SQL :: Insert All Rows Values Fetched By Cursor?

Aug 12, 2010

I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.

what parameter or code I should used ?

I have marked with color row as below.

DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: Insert Into Select Or Cursor For 100k Rows

Jan 22, 2012

I am trying to insert 100K rows, I have written this proc using cursor. But it is saying anonymous block completed, and no rows are inserted. If I just run the select it returns the rows.Could if just insert into select would be fine or should I use cursor.

CREATE OR replace PROCEDURE Insert_data (l_from_date IN VARCHAR2,
l_to_date IN VARCHAR2)
IS
lc_err_msg VARCHAR2 (2000);
ln_count NUMBER := 0;
CURSOR ins_d IS
SELECT a.col1 AS url,
b.col1 AS ref_url,
COUNT (*) AS total_views
[code]....

View 5 Replies View Related

Server Utilities :: Insert Rows In Two Tables Using Sql Loader?

Aug 7, 2012

I am trying to insert rows in two tables using sql loader.

I have two tables in database as

SQL> desc name
Name Null? Type
---------------------- -------- ------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE

SQL> desc name3
Name Null? Type
--------------------- ----------- -------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE

I created controlfiles as

[oracle@DBTEST sqldri]$ cat datafile.ctl
options (direct=true)
load data
INFILE *
into table name truncate
when id='1'

[code]....

when i run sql loader as

[oracle@DBTEST sqldri]$ sqlldr hr/hr control=/u01/sqldri/datafile.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 2.

no rows is inserted..the log file contain entries as

[oracle@DBTEST sqldri]$ cat datafile.log
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: /u01/sqldri/datafile.ctl
Data File: /u01/sqldri/datafile.ctl
Bad File: /u01/sqldri/datafile.bad

[code]....

View 7 Replies View Related

Number Of Rows Inserted Is Different In Bulk Insert Using Select Statement

Jul 1, 2010

I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.

strQuery :='INSERT INTO TAB3
(SELECT t1.c1,t2.c2
FROM TAB1 t1, TAB2 t2
WHERE t1.c1 = t2.c1
AND t1.c3 between 10 and 15 AND)' ....... some other conditions.

EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.

On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.

View 3 Replies View Related

Application Express :: What Would Cause Oracle To Insert Duplicate Rows Into A Table

May 2, 2013

What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.

View 9 Replies View Related

Precompilers, OCI & OCCI :: Insert Multiple Rows Using Array Bind Feature?

Aug 18, 2010

I am trying to insert multiple rows using array bind feature.

The following is working fine & inserting rows as expected.

char values[3][2] = { "1", "2", "3" };
ub2 lenp[3];
lenp[0] = lenp[1] = lenp[2] = 2;

[Code]...

OCIStmtExecute() is failing with the following error

Error - ORA-01480: trailing null missing from STR bind value

View 1 Replies View Related

Server Utilities :: Control File - Insert Rows Into Table By Defaulting Date?

Jan 15, 2013

My Table structure

column1 varchar(10)
column2 Date
Column3 varcahr(2)
Column4 varcahr(2)

By Data file

asds 12/12/2001asas
textsd asds
asds 12/12/2001asas
ramkiy asds

I still want to insert row 2 and row 4 into table by defaulting the date. how can I handle this in control file?

View 14 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.

CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv

[code]...

View 2 Replies View Related

Server Utilities :: Insert Data Without Writing Insert Statement In Oracle?

May 15, 2010

how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.

View 2 Replies View Related

Client Tools :: INSERT INTO With GUI - Insert Record Greyed Out?

Oct 28, 2013

I wish to make this simple statement with Toad GUI

INSERT INTO EXCLUDE_xxx
VALUES ('xxx',
'xxx',
'xxx',
'xxx',
SYSDATE);

Insert record is greyed out. How to insert new rows with Toad (click click)?

View 7 Replies View Related

Forms :: When Insert Detail The Data Insert Twice Time

Feb 27, 2012

when i tried to insert the details from oracle froms..the data inserts twice to the DB..

my table structure:

create table app_sri
(a_id integer primary key,
p_first_name varchar2(30),
p_last_name varchar2(20),
p_age number(3)
);

here a_id can be genarated through simple sequence(pid_seq)...
trigger on app_sri
create or replace trigger pid_trg

[Code]....

form insertion code..

Begin
insert into app_sri(null,'robo','Big',100);
commit
End;

the data inserted...but twice

what is the reason behind the double insertion?

View 8 Replies View Related

SQL & PL/SQL :: How To Insert Single Quote In Insert Statement

Feb 24, 2012

INSERT INTO LKP_ASSET_LOCATION (LOCATION) VALUES ('AMERICA'S CUP VILLAGE')

View 2 Replies View Related

SQL & PL/SQL :: Compare Current Rows With Previous Rows

Apr 12, 2013

The Table having 3 columns

col0col1 , col2
P112
P112
P102
P113
P115

I want to retrieve the changes rows only.

Output like
col0col1 , col2
P112
P102
P113
P115

View 5 Replies View Related

Select Insert Failing To Insert In 11g?

May 7, 2013

In 11g, When I am trying to insert the records with select insert option it is failing.

Below is my Query:

insert into table_1 select * from table_2

The above query is not inserting any records into table_1. But when i query select * from table it is returning records.

The same select Insert query is inserting records properly in 10g.

View 5 Replies View Related

Multiple Record Insert Using INSERT ALL

Jan 2, 2009

I'm trying to insert only a few columns (not all of them) from temp_ioi_010209 into mtl_system_items_interface. Both of these tables have more columns than just the 7 I'm specifying but everything I found under INSERT ALL here makes me think I'm doing it right. According to the DESCRIBE of mtl_system_items_interface the only non-nullable column is set_process_id and I'm specifying that one...

Why I'm getting the error "ORA-00947: not enough values"?

INSERT ALL INTO mtl_system_items_interface
VALUES
(process_flag,
transaction_type,
set_process_id,
[code].......

View 3 Replies View Related

SQL & PL/SQL :: Insert Statements / Give A Commit One By One After Each Insert Statements?

Oct 11, 2012

Can we execute more than one insert statements at a time (eg 10) in database and givecommit at the end of insert statements or else give a commit one by one after each insert statements ?

View 8 Replies View Related

PL/SQL :: Select 4 Rows And Then Next 4 Rows?

May 27, 2013

in my sql we can use limit to select first 4 rows in the table then next 4 rows ,can oracle do that ?

I have fifty rows inside the table

select * from student where rownum between 0 and 4 order by stuid ->it work

select * from student where rownum between 5 and 9 order by stuid -> did not work

what is the correct way to do it ?

View 10 Replies View Related

SQL & PL/SQL :: Subdivide Rows Into Rows

Apr 10, 2013

drop table test
/
create table test
(
lib varchar2(100)
)
/
insert into test values ('111/aaa/bbb/ccc');
insert into test values ('222/aaa/bbb/ccc');
insert into test values ('333+444/aaa/bbb/ccc');
insert into test values ('333/aaa/bbb/ccc');
insert into test values ('222+333+444/aaa/bbb/ccc');
insert into test values ('222+333+444+555/aaa/bbb/ccc');

I want to get this :

lib count
--------------- -----
111/aaa/bbb/ccc 1
222/aaa/bbb/ccc 3
333/aaa/bbb/ccc 4
444/aaa/bbb/ccc 3
555/aaa/bbb/ccc 1

View 4 Replies View Related

Delete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?

Dec 14, 2007

I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved