SQL & PL/SQL :: Insert Select Statement Limit Inserts?

Apr 7, 2011

this is my problem,

insert into t1
select *
from t2
where condition;

Select * from t2 where condition retrieves in example 1000000 records but I only need to insert into t1 the first 100 records.

Is there anyway to abort/limit the insert into t1 to 100 records?

This can´t be used because only applies the condition to the first 100 rows of t2 and it's not valid.

insert into t1
select *
from t2
where condition
where rowcount<100;

View 16 Replies


ADVERTISEMENT

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 :: Insert Statement In Trigger Accessing And A Select From A Table

Jun 6, 2010

In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.

But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger :
-------

IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN
INSERT INTO TEST.W_THRESHOLD_LOG
(THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE)
VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW);
END IF;
-------

In the above code if the hardcoded value 'Customer1' need to be picked from another table,
i.e .

SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID

how can I rewrite my query to the above value from the select into my insert statement..?

View 24 Replies View Related

PL/SQL :: Combining Identifier And Select Statement In Insert Into Command

Dec 11, 2012

Is there any way to combine an identifier and select statement in PL/SQL when using the insert into command?

e.g.

DECLARE
name := 'BOB';
BEGIN
insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP) name, (select min(time), max(time) from lLAP_TIMES);
END;

In the above statement I am trying to insert the identifier "name" (BOB) into MYTABLE.NAME, along with the result of the select query from the table LAP_TIMES ... min(time) into MYTABLE.SLOWEST_LAP, and, .... max(time) into MYTABLE.FASTEST_LAP.

If the above is possible in one statement how would I also combine an identifier, with two select statements into an "insert into" statement?

e.g.

name := 'BOB';
insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP, EVENT) name, (select min(time), max(time) from LAP_TIMES), (select race_event from MEET);

In the above example I am also trying to insert the result from MEET.RACE_EVENT into the column MYTABLE.EVENT

View 4 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

PL/SQL :: Save Exceptions Clause Or Equivalent For Insert As Select Statement

Jan 29, 2013

Is there a save exceptions clause or an equivalent for an Insert as select* statement ?

How do I trap the errors in the below statement -

INSERT INTO copy_emp
SELECT * FROM emp;

Is it an all or nothing scenario ?

View 10 Replies View Related

SQL & PL/SQL :: Insert Data Into Another Table With FORALL Bulk Collect Limit 1000

Aug 21, 2011

Table contains 10k records,we are going to insert data into another table with FORALL bulk collect limit 1000. if i use 10000 ,it's completed fast compared to 1000 limit.Can u tell me which one is better Limit.

View 4 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

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 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 :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

Jan 11, 2012

I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).

When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:

SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...

From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?

View 10 Replies View Related

SQL & PL/SQL :: Why Blind Select Is Better Than Conditional Select Statement

Dec 29, 2010

Why Blind select is better than Conditional select Statement?

View 10 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

SQL & PL/SQL :: Insert Statement?

May 13, 2011

I have one query i have create one table T1 in this table i have this three colm (EMPNO,ENAME,HIREDATE) and i have to insert raw from this two table T2 and T3 in T2 table colms are (EMPNO,SAL) and T3 table colm are (EMPNO,MGRID) so which query i have to run

View 4 Replies View Related

SQL & PL/SQL :: Null In Insert Statement?

Jul 5, 2010

I wanted to print 'null' when the column value is null. Actually, i am doing something like this

select empno||','||''''||ename||'''''||','||comm||','||sal from emp

It gives the following output for example
7369,'pointers',,200
If I use the above values to form a insert statement it throws
an error. As 'comm' value is not there.

I wish to get something like
7369,'pointers','',200
or
7369,'pointers',null,200
from the above select query

note I dint copy paste the query exactly from my sql*plus session as I am away from my oracle machine

View 14 Replies View Related

SQL & PL/SQL :: Using Subquery In Insert Statement

Jun 5, 2010

I read in a book that you can't use subquery in an insert statement . E.g:

1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:

2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');

This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.

View 5 Replies View Related

Insert Statement Causing ORA-01858?

Dec 4, 2012

i'm executing a load test using the below statement:

INSERT INTO BPMBI.PPROCINSTANCE SELECT * FROM BPMBI.PPROCINSTANCE_BKP WHERE ROWNUM < 501;
COMMIT;
EXIT;

I've been using the same for a few days without any error while now wehn i try to use the same i get below error:

SQL> insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500;
insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected.

I've been trying to check what the issue is and when i execute the rownum < 140 works fine while rownum < 150 is showing the same error.

View 6 Replies View Related

Concurrency Wait On Insert Statement?

Feb 19, 2013

I am running Oracle RAC 2 nodes 11g R2 on AIX 7.1

I have a table with unique index, and the application is doing inserts/updates into this table.Suddenly and for about half a minute I faced a high concurrency waits on all the processes running these inserts for one node. I saw this high concurrency wait in the top activity screen of the OEM only on one of the nodes. knowing that the processes doing these inserts are running on both nodes.

All what I have that in this half minute I see high concurrency wait in OEM top activity screen related to this insert statement and when I clicked on the insert I found high "enq: TX - index contention". Again this was only on one node.After this half minute everything went back to normal.What could be the reason and how can I investigate it ?

View 3 Replies View Related

PL/SQL :: Using Sqlerrm Directly On Insert Statement?

Oct 3, 2013

Given below a block of code, this code compiled successfully on one DB but returns error on other.DB version is same. I know sqlerrm can not be used directly but how it compiled successfully on one DB. 

declare
l_procedure_name CONSTANT VARCHAR2(100) := 'copy_device_status_tables';
l_procedure_id CONSTANT INTEGER := 301;

[Code].....

Error report:

ORA-06550: line 12, column 75:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 11, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.

View 5 Replies View Related

SQL & PL/SQL :: Insert Statement Which Will Commit Transaction By Itself

May 22, 2010

What is the syntax to write an insert statement which will commit the transaction by it self,not using an exclusive commit statement after wards.

View 2 Replies View Related

PL/SQL :: Exception Handling In Insert Statement

Jul 17, 2012

Here's sample code :

declare
i number;
l_rec number;
cursor c1 is select i from t1;
begin
[code]....

After executing Data must be inserted into t2..If any error is there it should insert into t2 with status 'E' and move on T2

i fflag
---------
11 E
111 Y
1111 Y

The code above is failing after inserting 11 E into t2 table

View 5 Replies View Related

Insert Into Statement - Getting ORACLE Error?

Jan 22, 2012

I am creating the following two tables...no issues here:
CODECREATE TABLE COURSE_SECTION
  (
  Csecid NUMBER(8) CONSTRAINT COURSE_SELECTION_NUMBER_pk PRIMARY Key,
  Cid NUMBER(6) NOT NULL CONSTRAINTS COURSE_SELECTION_Cid_fk REFERENCES COURSE,
  Termid NUMBER(5) NOT NULL CONSTRAINTS COURSE_SELECTION_Termid_fk REFERENCES TERM,
 
[code]...

The issue I am having is actually inserting data into the table:
CODEINSERT INTO ENROLLMENT
  VALUES (100, 1000, 'A' );
INSERT INTO ENROLLMENT
  VALUES (100, 1003, 'A' );

[code]...

But I get an ORACLE error of

ORA-02291- integrity constraint (User1.ENROLLMENT_CSECID_FK) violated - parent key not foundHow can the parent key not be found when I have it declared/created in the above statement?

View 3 Replies View Related

If Statement In Select?

Mar 22, 2013

simply select and works great:

select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,

substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,

case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2

from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id

where UPPER(ISSUE_STATUS) like '%OPEN%'

Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2

if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?

View 4 Replies View Related

Using Of ROWNUM In Insert Statement Gives Error In Oracle 11g

May 24, 2011

I have a query regarding the use of rownum inside the insert statement.

For example, I have a sample table as: sample1(aa date, bb number);

Insert
INTO sample1
VALUES (SYSDATE, ROWNUM);

this statement is working fine in Oracle 9i but gives error in Oracle 11.2.0.1. The error is ORA-976 ,

Why this error coming in Oracle 11g and how to resolve it?

Our Environment: UNIX AIX 5.3, Oracle 11.2.0.1 database

View 1 Replies View Related

Forms :: How To Insert And Update With Single Statement

Mar 5, 2012

I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )

example of scenario...

date s1 s2 s3

in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.

View 8 Replies View Related

SQL & PL/SQL :: Inserting Image Into Table By Insert Statement

Sep 30, 2010

I have a table with a column type long raw. Now i want to insert an image from directory by an insert statement. Is it possible?

View 2 Replies View Related

SQL & PL/SQL :: Remote Grant Through Insert Statement Into Sys Table?

Jun 18, 2013

I want to grant a privilege through an insert statement into a sys table.Why do not grant the privilege through the classic way : grant select on t to l_user; ?

Because I want to do it remotely.I am connected to db1.I want to grant select on t2 to u2_b from u2_a.I assume that all DDL are DML. So a grant is equivalent "somewhere" to an insert.I tried to do my requirement locally, and here is the output.

SQL> conn scott/aa
Connecté.
SQL> -- step 1 : try to grant "normally" a select on dept to hr from scott
SQL> grant select on dept to hr;

Autorisation de privilèges (GRANT) acceptée.

SQL>
SQL> conn sys/a as sysdba
Connecté.
SQL> -- step 2 : Then, we connect to sys to see the row inserted in dba_tab_privs
SQL>
SQL> col GRANTEE format A10
SQL> col OWNER format A10

[code]...

Then if I can do it locally, I can do it remotely through a db link.

View 2 Replies View Related

SQL & PL/SQL :: Generating Increasing Number In Insert Statement

Mar 6, 2012

we are inserting a data in table by selecting data from another table.This is part of a very big procedure and the code is like

Loop
SELECTMAX(biz_trn_no) + 1
INTOv_biz_tran_no
FROMnew_business_transactions
WHEREnb_srno = nb_sr_no;
[code]....

Is there any way I can get this functionality without using a sequence? I assume corelated query would be slow and analytic.

View 6 Replies View Related

SQL & PL/SQL :: Difference Between After / Before Insert In Row Or Statement Level Trigger

Feb 8, 2012

what is the difference between after or before insert in row level trigger and statement level trigger.

View 3 Replies View Related







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