How To Make Multiple Inserts In DB

Sep 4, 2009

i have multiple inserts to make in a table that is in an Oracle database...i already try several ways to do it but it always giving erros... how to make multiple inserts at same time.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Improve Procedure Which Is Looping Through Query To Make Inserts

Oct 10, 2013

I am trying to improve a procedure which is looping through a query to make inserts.

FOR P IN (
SELECT O.TYPEID
,o.KEY
,O.ID
,O.NAME
,O.LGNUM
,O.LGNAME
[code]....

View 12 Replies View Related

Client Tools :: Bash Script - Extract Fields And Make Inserts In SQL File

Aug 13, 2010

I have a Bash script that counts the rows of a csv file, extracts the fields and makes inserts in a sql file. Then it logs into SqlPlus and calls the insert file. The sql file looks like this:

WHENEVER SQLERROR EXIT
INSERT INTO SCHEMA.TABLE1 (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
[code]....

I rely on "WHENEVER SQLERROR EXIT" for things to go the right path. However sometimes because of the contents of the CVS files (which I can't control) some rows don't get inserted but SqlPlus doesn't see that as an error, doesn't exit and I end up with the wrong number of rows being informed in the second insert.Is there some kind of "if-then-else" construct in Sql? After all the inserts are made, do a "select count (*)" and compare that number to the one informed by the script. If they match, make the final insert and commit; else exit.

View 9 Replies View Related

SQL & PL/SQL :: Automate Inserts From One Schema To Another

Dec 18, 2012

We have two databases one localdb with user rakdb and another one remotely remotedb with user rakdb .We need to be in sync with data in one table called om_item, where the users are inserting data on daily basis and the user sends us the insert script everday to run it on local databse to insert the new records in local database.I managed to create a file which records all the inserts into one text file in one directory.Can we have a scheduler to pick this text file from the specified folder and send mail using utl_mail.

CREATE TABLE ITEM (IT_CODE VARCHAR2(12),IT_NAME VARCHAR2(20));
INSERT INTO ITEM VALUES ('A','AAA');
CREATE OR REPLACE DIRECTORY MY_DIR AS 'C:TEMP';
CREATE OR REPLACE PROCEDURE it_status

[Code]..

Procedure created.

EXEC it_status
HOST TYPE c: empaaaa.txt
INSERT INTO ITEM (IT_CODE, ITEM_NAME) VALUES ('A','AAA');
COMMIT;

View 4 Replies View Related

Parameters For Table Containing Only Inserts

Apr 1, 2013

I'm on 11.2 DB and need to create an audit table that will be populated by DB triggers of other tables (after Insert,Update and Delete). The triggers will only ever be inserting data into the audit table. I have read that for insert only tables, you should define the 'pctfree' as 0. Is this correct? Do I need to set any other params (like pct_used) for tables only ever being inserted into?

View 2 Replies View Related

SQL & PL/SQL :: Performance Tuning In Large Inserts

Jan 19, 2011

I want to load 10 millions records from staging table to master table.One logic must be take during the load, the logic is rows already present in master table means, we need to update corresponding rows in master table otherwise rows insert in target table.

I have been using bulk collect and forall method to load data. it shows better performance compare then cursor row by row process. As per oracle doucmentation, we cannot use SELECT statements inside FORALL condition so we could not use logic inside the forall condition.

View 2 Replies View Related

SQL & PL/SQL :: Inserts Based On Column Values

Aug 15, 2012

I have a data in one table with 6 columns where user may be updating values in all of these 6 columns or he may enter 3 or 4 columns based on that inserts should take place, this is similar to my previous thread , i am using if condition to check column for null if its not null then i will make a insert , but is there any other easier way to do this.

CREATE TABLE ot_inspect_head
(inh_sys NUMBER,i_txn_code VARCHAR2(12),i_no NUMBER,i_ref_txn VARCHAR2(20),i_ref_no NUMBER);
CREATE SEQUENCE inh_sys START WITH 1;
CREATE TABLE ot_inspect_item
(ii_inh_sys NUMBER , ii_pi_sys NUMBER,ii_sys NUMBER,ii_item_cd VARCHAR2(12),
ii_grade VARCHAR2(12),ii_qty number, ii_flex_01 VARCHAR2(12),ii_flex01_qty number,
ii_flex_02 VARCHAR2(12),ii_flex02_qty number,ii_flex_03 VARCHAR2(12),ii_flex03_qty number);
CREATE SEQUENCE ii_sys START WITH 1;

insert into ot_po values ('ss-po',1,ph_sys.nextval);
insert into ot_inspect_head values (inh_sys.nextval,'ss-ins',1,'ss-po',1);
commit;
select * from ot_inspect_item

II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
2 1 2 HEA100 A 100

--Now if the inspection user issues the update statement , it will delete this row
--from ot_inspect_item and reinserts the values with values based on
--ii_flex_01,ii_flex_02,ii_flex_03
[code]...

View 5 Replies View Related

SQL & PL/SQL :: Missing Records While Performing Parallel Inserts

Jun 29, 2010

I have prepared shell scripts to do the parallel inserts on my DB table (LEGACY_SYSTEM).

There is a trigger (AFTER INSERT ON EACH ROW) associated with the above table. I am calling a package.function inside the trigger to do the required operation and finally it will insert records into my target table (PRICE_CHANGE).

Expectation:
------------
If I insert 10 rows into LEGACY_SYSTEM table, it should do few updates and finally insert 10 rows into PRICE_CHANGE table.

Result:
-------
10 rows got inserted into LEGACY_SYSTEM. All the updates are successful but I could see only 4 rows in PRICE_CHANGE table. If I run it for the second or third time, all the results will be perfect.

Instead of these shell script, if I insert one by one rows manually into LEGACY_SYSTEM table, I am getting all the expected results and the results are consistent. If you look at my scripts below, you will understand the problem better..

DRIVING SCRIPT(test_global.sh):
---------------
#!/bin/sh
./test_script.sh &
./test_script2.sh &
exit;

test_script.sh:
---------------
#!/bin/sh
./test.sh 100052051 10974 &
./test.sh 100052051 8649 &
./test.sh 100052060 10974 &
./test.sh 100052060 8649 &
./test.sh 100052078 10974 &
./test.sh 100052078 8649 &
./test.sh 100052086 10974 &
./test.sh 100052086 8649 &
exit;

test_script2.sh:
---------------
#!/bin/sh
./test.sh 100052051 11698 &
./test.sh 100052051 11777 &
./test.sh 100052060 11698 &
./test.sh 100052060 11777 &
./test.sh 100052078 11698 &
./test.sh 100052078 11777 &
./test.sh 100052086 11698 &
exit;

test.sh:
--------
#!/bin/sh
sqlplus HRUAMGR/HRUAMGR@MALDEV_DW1<<EOF
ALTER SESSION ENABLE PARALLEL DML;
insert into legacy_system (product, item, supp, type, g_ind, d_ind, o_num) values (7896544,$1,$2,'TEST',NULL,NULL,NULL);
commit;
exit;
EOF

I am calling test_global.sh through the UNIX session and all the records got inserted into LEGACY_SYSTEM table and few rows are missing from PRICE_CHANGE table.

If I remove the '&' symbol and execute, the results are perfect. But the requirement is not to remove the '&' symbol. I have been facing this problem for the past 1 month.

View 8 Replies View Related

SQL & PL/SQL :: Create Counter For The Number Of Inserts Run In A Block

Feb 18, 2010

I have a plsql Proc, which accepts a few parameters and inevitably loops through a cursor and runs a bunch of insert statements. With quite a few IF conditions.

Each insert statement has a value which i want to increment by (+1) every time an insert statement is executed in the same loop.. This is for a student housing database and this is for their room preferences so 1 is the first, 2 is there second preference e.t.c.

Please take a look at the code below: in the Insert values() I have put a? Where I want the number to increment from.

There are a lot more inserts which I haven't put below. I hope I have made myself clear as this has been quite difficult to explain. So for example if the 2nd two inserts are run, then I was the first one to insert with a 1 and the second with a 2.

BEGIN
FOR rec IN c1
LOOP
IF c1%FOUND THEN
INSERT INTO table (PK_A, fk_rms_id, application_type, application_person_type) VALUES (NULL, rec.pk_rms_id, app_type, app_person_type) RETURNING PK_APPLICATION_NO INTO x;
[Code] ........

View 2 Replies View Related

SQL & PL/SQL :: Create A Procedure That Inserts Parameters Into A Table

Oct 20, 2011

I am trying to create a procedure that inserts parameters into a table and then returns the number of rows inserted back to calling block. the procedure is compiling fine but is not returning the number of rows inserted. My code is as follows;

HOST VARIABLE DECLARATION
VARIABLE g_CarMasterInsertCount NUMBER;

STORED PROCEDURE
CREATE OR REPLACE PROCEDURE CarMasterInsert_sp (
registration IN VARCHAR2,
model_name IN VARCHAR2,
car_group_name IN VARCHAR2,
date_bought IN DATE,
cost IN NUMBER,
miles_to_date IN NUMBER,
miles_last_service IN NUMBER,
status IN CHAR,
rowsInserted OUT NUMBER)
[code]....

I think im close just that my syntax is off.

View 8 Replies View Related

SQL & PL/SQL :: Find Number Of Inserts Happened Every Day To A Particular Table?

Aug 9, 2013

Is there any way I could find the number of inserts happened every day to a particular table.

View 10 Replies View Related

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

PL/SQL :: Commit Loop - Brought Up As To Max Inserts Per Transactions In 11g?

Nov 2, 2012

I'm running 11.2.0I am looking at tuning a sql statement, and the question was brought up as to the max inserts per transactions in 11g, and if it exceeds 1000.I haven't found a solid answer yet, but I thought that 10g was higher than 1000.

My first thought was to implement a commit loop on every 1000 rows, as that is how things were handled in the past.But I found an article that talks about redo logs and performance and how it's a horrible practice to do the commit loop.

What I haven't found is what is the better methodology in doing this?My scenario could encounter inserts as much as 20,000 at a time.

View 9 Replies View Related

SQL & PL/SQL :: Bulk Inserts Get Extremely Slow After 10000 Queries?

Feb 20, 2010

I am running a custom script that creates about 100,000 rows of demo data.

The table I am loading in to is fairly wide (100 columns), and only has about 10,000 rows at the moment.

The script goes really fast for the first 10K rows (100 inserts per second), and then incrementally gets slower. By 20,000 rows it is doing about 1 row per second. At this rate, it will never finish!.

Each insert is a separate statement, using bind variables and wrapped in a single transaction. I've tried dropping the indexes first but it didn't make a difference.

OEM shows it's 100% CPU bottleneck with no other information I can glean.

View 32 Replies View Related

Performance Tuning :: How To Control Concurrent Inserts Of Record By Parallel Sessions

Aug 7, 2012

There is a detail table

There is a summary table

During batch process record is entered in detail table as well as summary table.

The process first checks if record exists in summary table for same group_no and if 'yes' then "updates" the record with the newly added amount (sums it) else inserts a new record
Whereas in the detail table it inserts the record directly

detail table :
group_no doc_no amount
101 doc1 100
101 doc2 200
102 doc3 300
102 doc4 400

summary table :
group_no amount
101 300
102 700

Now if the batch process runs in parallel, (out of many) two different sessions insert same group_no; This is because while sesond session inserts a record, first session inserting the same record (group_no) has not yet committed ; So second session Not knowing that already there is same Group_no (101) inserted, again inserts another record with same group_no rather than summing it.

Can it be solved without using temp table, select for update?

View 4 Replies View Related

SQL & PL/SQL :: Write A Script That Automatically Increments And Inserts Values For Already Existing Rows

Aug 21, 2012

I have a table named student_details with columns "NAME","ADDRESS","COURSE" with several rows of data already insertedI have to add one more column "ID" which increments automatically.

I tried to do this using SEQUENCE but no values got inserted for already existing rows in "ID". how to write a script that automatically increments and inserts values for already existing rows also.

View 12 Replies View Related

PL/SQL :: Track Table Data Changes (inserts / Updates / Deletes) Including Os_user

Mar 12, 2013

I was given a task by manager to keep track of changes on a given table including os_user who made it.Should I create a trigger on it (on any update, insert, delete etc.) or there is a better way of doing it ?I think there could be some info already in some data dictionary views or something like it.

If I CREATE MATERIALIZED VIEW LOG on that table.

View 4 Replies View Related

Server Utilities :: Loading Multiple Input Files Into Multiple Tables

Jul 9, 2012

NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'

File :-mauri.export.ngf

NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc

CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......

find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.

View 6 Replies View Related

Server Administration :: Multiple Database With Multiple Instance On Same Machine?

Mar 16, 2011

can we have multiple database version running in a single machine with multiple instances provided there are enough resources.Can we do in RAC only?

View 15 Replies View Related

SQL & PL/SQL :: How To Insert Data In Multiple Bases Using Multiple Database Links

Jan 2, 2013

how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?

View 4 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

SQL & PL/SQL :: Multiple Rows On A Table To Multiple Columns On One Row

Nov 26, 2010

I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below

TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan

My desired result is to be able to show the output as follows

Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan

View 6 Replies View Related

SQL & PL/SQL :: How To Make PK Not To Validate

Nov 21, 2011

i am writing a script for inserting rows in a table where primary key of that table consists of 4 columns. I want to add a new row where 3 columns of that table will be same but change the value of the last one. this is giving an error saying that i am violating the unique constraint. I tried to disable that constraint/PK by following statement but still this error is coming

alter table sections_budgets modify primary key disable cascade;

OR

alter table sections_budgets modify constraints sec_bdgt_pk disable cascade;

the insert statement which i am using. The first four columns are part of PK

insert into sections_budgets (SELECT sb.bdgt_cd, sb.bdgt_dpf_cd, sb.bdgt_yr, :sec_code, sb.usf_usr_id,
sb.aty, sb.comaty, sb.comytd, sb.expn_ytd, sb.sys_dt, sb.cat,
sb.start_aty, sb.expn_lmt
FROM sections_budgets sb
WHERE sb.bdgt_dpf_cd = :b_d_code AND sb.bdgt_yr = :year);

is there any other way of inserting records in this table without droping the whole table because it contains too many triggers and constraints?

View 24 Replies View Related

SQL & PL/SQL :: How To Make Report

May 2, 2010

How can I make report in below format ?

Month Apr-09 May-09 Jun-09 ...... Mar-10 Total

Basic 1000.00 1100.00 1300.00 ...... 1300.00 13260.00

HRA 500.00 600.00 650.00 ...... 600.00 6350.00

Conveyance 100.00 200.00 300.00 ...... 350.00 1300.00

Gross Amt 12350.00 13500.00 13200.00 ......14500.00 23456.00

View 9 Replies View Related

PL/SQL :: Multiple Rows To Multiple Column

May 6, 2013

I have a table TableA containing 2 columns ( Name and Value). Here I know what are the values for column Name

TABLEA
=======
Name Parameter
-------------------------
Nexus 11
GPlay 21
Demo 31

I need a query which provides the below output

Desired Output:
======
First Second Third
11 21 31

I have tried the below query
SELECT
DECODE (name,'Nexus', parameter) First,
DECODE (name, 'GPlay', parameter) Second,
DECODE (name, 'Demo', parameter) Third
FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));

This gives me the output

First Second Third
11 <Empty> <empty>
<empty> 21 <empty?>
<empty?> <empty?> 31

Is there any way to get the output in single line.

View 3 Replies View Related

How To Make Sure Checkbox Checked Or Not

Apr 19, 2007

On a page I have let's say 5 rows (can be more or less) and each row ends with a checkbox. On the next page(next prcodure) I go through all rows, but I get a "No data found" when a checkbox is not checked. Checkbox's parametre is send using an array.How can I make sure is a checkbox checked or not. If it's not, I put a formHidden through in it's place?

View 1 Replies View Related

SQL & PL/SQL :: How To Make Horizontal Partitioning

Mar 4, 2013

how should i do to make horizontal partitioning?

View 18 Replies View Related

SQL & PL/SQL :: How To Make Columns Out Of Records

Feb 8, 2013

I want to learn how to make columns out of records?

View 8 Replies View Related

SQL & PL/SQL :: How To Make A Call To A Proc

Mar 24, 2010

How to make a call to a proc and immediately return without waiting for the called proc to end.

Nothing I try seems to do the trick...The calling program always has to wait.

View 6 Replies View Related

Forms :: How To Make A Calendar

Mar 4, 2003

I have read the messages below about making a calendar but am still unsure of how to go about doing this. I have the STNDRD20.OLB file, but I don't know where to put it or what to do with it! What comes next?

View 20 Replies View Related







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