SQL & PL/SQL :: Inserting Row From Table1 To Duplicate Table2

Aug 3, 2010

there are a number of ways I can do this, but I'm just posting this here incase any of you plsql experts know of the best way to program this.

Basically I have 2 tables

INT_CASH_RECORDS

TMP_CASH_RECORDS

Both these tables have exactly the same number of fields and field types - both tables are literally the same. The primary key in both tables is a field called 'cash_id'

How can I transfer a record from INT_CASH_RECORDS into TMP_CASH_RECORDS based in a cash_id, I'm looking for the query string, something like

insert into tmp_cash_records (select * from int_cash_records where cash_id='3342' ...)

View 10 Replies


ADVERTISEMENT

PL/SQL :: Update Two Columns In TABLE2 From TABLE1

Jan 25, 2013

I know this is a simple question for some of you, but I am new to SQLs,

I have two tables TABLE1 & TABLE2 as below, both tables contains more then 50million records:

SELECT * FROM TABLE1.
&&&&&&&&&&&&&&&&&&&&&&&&&&&
ID                        BUS_FID                WORKID                  STATIONID                 
---------------------- ---------------------- ---------------------- ----------------------
28400000117234         245                    13461428.25           16520877.8            
28400000117513         403                    13461428.25           16520877.8            
28400000117533         423                    13461428.25           16520877.8            
28400000117578         468                    13461428.25           16520877.8            
28400000117582         472                    13461428.25           16520877.8            

SELECT * FROM TABLE2.
&&&&&&&&&&&&&&&&&&&&&&&&&&&
BUS_FID                    ID                 TRPELID                RELPOS                 WORKID                 STATIONID               
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
114                    28400000117658         28400000035396         23.225                                                              
115                    28400000117659         28400000035396         23.225                                                              
116                    28400000117660         28400000035396         23.225                                                              
117                    28400000117661         28400000035396         23.225                                                              
118                    28400000117662         28400000035396         23.225                                                              
119                    28400000117663         28400000035396         23.225                                                              
120                    28400000117664         28400000035396         23.225                                                              

[Code]....

Now I tried to use following SQL to update WORKID & STATIONID columns in TABLE2 but failed. BUS_FID in both tables have been UNIQUE indexed and they can be used as primary keys to join these two tables.

UPDATE (
  SELECT  p.WORKID px,
          p.STATIONID py,
          p.BUS_FID pid,
          temp.WORKID tempx,
          temp.STATIONID tempy,
  
[Code]....

with above code, Oracle returned following errors:

SQL Error: ORA-00904: "TEMPID": invalid identifier
00904. 00000 -  "%s: invalid identifier"

BTW, both two tables contains over 50 million records. So, if you have a better SQL to perform the same task.

View 6 Replies View Related

PL/SQL :: Dump Values From One Table1 To Table2

Sep 14, 2012

table1-- EMPLOYEE
empno          ename        dept
001            david             20
002            thomas         30

I need query to dump all the values to below table as show below

table2 -- DEPT
empno      info
001          david
001          20
002          thomas
002          30

View 10 Replies View Related

SQL & PL/SQL :: Show Total Of Details - Table1 In Table2?

May 17, 2011

I got table alpha

CREATE TABLE alpha
(alpha_id NUMBER
alpha_cash NUMBER,
alpha_other VARCHAR2(20)
);

[Code]....

What is the best/a good way to display in alpha the total regarding the alpha_id ?

View 1 Replies View Related

SQL & PL/SQL :: Trigger To Update Table1 Based On Condition Of Values In Table2

Feb 20, 2012

I have two tables as

Table LEAVE
Column Type Null Description
APP_NO Number(6,0) Not Null PK Leave Application Number
ECN Number(6,0) Not Null FK Employee Code Number
APP_Date Date Not Null Date of Application
From_Date Date Not Null Date from which the leave starts
TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date
NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date
LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA
Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved
Remark VARCHAR2(100) Nullable Reason to be put if status is rejected
[code]....

What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the _USED values of the corresponding LEAVE_TYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.

For example if 3 rows are inserted in the LEAVES table as
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null);
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null);
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');

Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_ UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'

I tried with the following trigger, but is compiling with a warning (not showing what the warning is)

CREATE OR REPLACE TRIGGER leaveentitle
AFTER INSERT ON LEAVES
FOR EACH ROW
BEGIN
UPDATE LEAVEENTITLE LVE
SET LVE.SL_USED = SL_USED+(CASE
WHEN :NEW.LEAVE_TYPE = 'SL'&& NEW.STATUS='APPROVED'
THEN :NEW.NO_OF_DAYS
SL_UNUSED=SL_ENTITLED - SL_USED
ELSE 0
END),
[code]....

View 9 Replies View Related

Server Utilities :: SQL Loader - How To Ignore Inserting Duplicate Records

Apr 9, 2010

I'm calling sql loader recursively to load data from CSV files which has thousands of records in each file. If there are any duplicate records, the sql loader terminates with ORA00001. My query is how to ignore inserting duplicate records and continue with the load.

Most of the posts in forums suggests to use skip command. But i do not think that is a wise option in my case as we cannot predict the maximum error count. more over I have set up ERROR=0 in my code so that the code terminates in case thers is a data error.

any other way to ignore inserting duplicate records into the tables.

View 7 Replies View Related

SQL & PL/SQL :: Writing A Procedure For Updating Table2 Based On Table3?

Sep 14, 2011

In my application i have a requirement as follows.I have 3 tables table1,table2,table3.I have 4 tickets for one license number which is related to client table as follows.

table1
=====
license_nbr(pk) name address
=============== ===== =======
LicNo1 test testing

Table2
=====
ticket number(pk) amountto be paid balance_amount license_nbr(fk table2)
tk1 200 200 0 LicNo1
tk2 300 300 0 LicNo1
tk3 400 400 0 LicNo1
tk4 500 500 400 LicNo1

table3
=====
payment table
ticket_number(fk table2) amount paid payment status license_nbr(fk table1)
tk1 1000 excess paid. LicNo1

so now the excess paid amount to be adjusted for the remaining tickets through tk2 to tk4.and only tk4 should remain with 400 the balance amount should be updated accordinglyand i have a license number which is a foreignkey of client table. writing a procedure for updating the table2 based on the table3.

View 14 Replies View Related

PL/SQL :: ORA-00001 / Unique Constraint Because ID Is Primary Key On TABLE1

Jun 27, 2012

I have in a plsql block somewhere a statement like

INSERT INTO TABLE1( id , col)
SELECT id, col
FROM TABLE2;

This statement returns an error ORA-00001: unique constraint because id is a primary key on TABLE1. I would like to know what is the value of id that raised the exception.

View 15 Replies View Related

SQL & PL/SQL :: Stop Inserting Data Into A Table Before It Inserting Using Trigger?

Jul 26, 2011

shall we stop inserting data into a table before it inserting using Trigger?

View 3 Replies View Related

SQL & PL/SQL :: Delete Table1 Rows Where 3 Fields To Join With Another Table

Aug 13, 2011

I need to delete all the registers where the table 1 does join with table 2 in 3 fields... for example:

delete taba1 t1
where t1.campo1 in ( select distinct(tr.campo1)
from tabla1 tr,
tabla2 t2
where t2.error = 0
tr.campo1 = t2.campo1
and tr.campo2 = t2.campo2

[Code]...

View 4 Replies View Related

Inserting In A View

Aug 13, 2010

I have a view ( from many tables) , an error view in wich i monitor errors that appear in my project. This view should be empt always but whenever a error occurs the view shows me this. I wanna put a trigger or something like that on that view to send me an e-mail whenever a line is inserted in that view. ( I don't wanna use a job for that to make a count because this will affect the entire database).

View 3 Replies View Related

Inserting Dates Into Table

Mar 10, 2012

My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.

This is my table:

DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE;
CREATE TABLE BookingDM (
hNo NUMBER(3),
gNo NUMBER(5),
dFrom DATE NOT NULL,
[code]......

This is the first set I'm attempting to insert
hNo = 148
gNo = 11169
dFrom = 09/03/2009
dTo = 09/10/2009
rNo = 202

This is my attempt to insert the set:

SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202');
INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202')
*
ERROR at line 1:
ORA-01843: not a valid month

I need my dates to be in the format "MM/DD/YYYY".

View 4 Replies View Related

Inserting Values From A Sequence?

Jun 10, 2011

I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'

Created a sequence starting with 1...

and inserted single row, and multiple rows using loop also; by using below code -

insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;

but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'

View 1 Replies View Related

Oracle SQL Plus Inserting Into A New Field?

May 29, 2007

Ive added a new field to a table The new field is called Release_confirmation. How do I add the same value to this field for all rows ie. confirm.

So Id basically be setting Release_confirmation to confirm for all existing rows.

Should I use update or insert?

View 2 Replies View Related

Inserting Data Into Two Tables

Feb 15, 2009

I have two tables

table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2

table2

col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key

now my question is how to insert data in to the two tables

View 2 Replies View Related

Inserting Cursor Into A Table

Aug 27, 2010

i am trying to fetch the cursor returned from a stored procedure and insert into a physical table. but i am unable to populate the table. below is how my code looks.

declare
p_out sysadm.CGF_PKG_REFCUR.rcDataCursor;
a table1.node%TYPE;
b table1.acct%TYPE;
c table1.descr%TYPE;
[code]......

In the above, stored_proc is the stored procedure from OLAP database. Above code i am executing is from EPM database.

p_out returns a result set of (select a,b,c from table2).

I am trying to get the result set from OLAP database and populate a table in the EPM database.

View 3 Replies View Related

Inserting Blank Data

Jul 17, 2009

I have a table in oracle sql developer showing years, IDs and attendance etc I'm wanting to create a report based on this table but have encountered a problem with the years.

The years currently run from 2006 to 2009 and the problem is that some of the IDs are only present for say 2007 and 2008, thus leaving nothing for 2006 and 09. This in turn creates problems in the report.

What i would like to do is in the table, for each ID that is missing any years, is to insert a single row with just the ID and year so that in the report it will show a blank instead of nothing at all. So for the above example i would have numbers for 2007 and 2008 and for 2006 and 2009 there would just be a space.

View 6 Replies View Related

SQL & PL/SQL :: Inserting Images Into Database

Dec 22, 2011

how to insert images into a table or database..i had created a column with BLOB datatype inside a table.

View 1 Replies View Related

SQL & PL/SQL :: Inserting Data Using DBLINK

Feb 21, 2013

I am inserting XMLTYPE data using DBLINK I am getting the following error.

INSERT INTO APSP.SALES_HISTORY@APSP_LINK
SELECT * FROM KMBS.SALES_HISTORY

ORA-22804: remote operations not permitted on object tables or user-defined type columns

Source table structure

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

Target table structure(another DB)

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

how to insert XMLTYPE data using DBLINK.

View 16 Replies View Related

SQL & PL/SQL :: Inserting Date Ranges

Apr 30, 2010

Can we insert multiple dates within give range in a single insert statement say from 1st Feb to 31st MAY 04,something like after insertion it should resemble like below

01-FEB-04
02-FEB-04
03-FEB-04
04-FEB-04
05-FEB-04
.
.
.
31-MAY-04

View 5 Replies View Related

SQL & PL/SQL :: For Loop - Inserting Data?

Mar 7, 2012

create table stg1(x number, y number);
create table stg2(x number, y number);
create table stg(x number, y number);

I want to insert data from stg1, stg2 into stg

Instead of writing two insert statements, I want to write only one in a for loop to insert data into stg from stg1 and stg2

I tried this
begin
for i in 1..2 loop
insert into stg(x,y) select stgi.x, stgi.y from stgi;
end loop;
end;

it gives me table does not exist error:

so by stgi, i mean it should take stg1, stg2 etc

View 4 Replies View Related

SQL & PL/SQL :: Inserting Data From One Table To Another?

Oct 23, 2010

I have two tables with two columns of each table in my Oracle Version :Oracle 9.2.0.1.0

TEST22:
-----------
|sno |sname |
-----------
| | |
-----------

TEST22P:
---------------
| col1 | col2 |
|---------------|
| sno | 1 |
| sname | arun|
---------------

Required outcome is

TEST22:
----------
|no | name |
|----------|
|1 |arun |
----------

Also this should be applicable for more than one value in the column col2 of table TEST22P.

ex:-
TEST22P:
--------------------
|col1 | col2 |
|--------------------|
|sno | 1,2..n |
|sname | arun,ajay..n|
--------------------

I used decode & pivot insert for this,but the result is a failure.

SQL>INSERT INTO test22 (no,name) SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;

SQL>
sno sname
--------
1 null
null arun

AND

SQL> INSERT ALL
2 INTO test22 VALUES(no)
3 INTO test22 VALUES(name)
4 SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
INTO test22 VALUES(name)
*
ERROR at line 3:
ORA-00904: "NAME": invalid identifier

View 4 Replies View Related

SQL & PL/SQL :: Inserting Single Quotes

Dec 4, 2012

I have a string: 'VOLT,AGE'..

How can I convert this string to: 'VOLT','AGE' using REGEXP_REPLACE...

I am having trouble escaping the single quotes in my query

View 6 Replies View Related

Inserting Into Table Values

Oct 6, 2012

I need to insert rows in a table all the columns are have same value except one column i.e.

insert into table values( 'a','b','b','b');
insert into table values( 'b','b','b','b');
insert into table values( 'c','b','b','b');

is there any short cut to insert because there are thousands of records to be inserted.

View 3 Replies View Related

SQL & PL/SQL :: Trigger Not Inserting The Records?

Jun 21, 2012

I have written a trigger which insert and update on same table and the select statement for update and insert is same. My trigger update the record but doesn't insert data and doesn't throw error as well.

if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex.

create or replace
TRIGGER SRVCCLLS_RVW
AFTER INSERT OR UPDATE OR DELETE ON SD_SERVICECALLS
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE

[Code]....

if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex

View 2 Replies View Related

PL/SQL :: Inserting In View Which Contains From Two Tables

Sep 25, 2012

There is an error after error, that's why I came here

What's is wrong? error message: virtual column not allowed here

CREATE TABLE test2(
id NUMBER PRIMARY KEY,
surname VARCHAR2(20) NOT NULL);
CREATE TABLE test1(

[Code]...

It would be nice to describe this situation and where does this error come from.

View 1 Replies View Related

PL/SQL :: Inserting PDF File Into Clob?

Oct 11, 2012

I tried inserting PDF file into a CLOB column , in the below example l_bfile is a bfile datatype. and I'm inserting l_bfile to CLOB column.

l_bfile := bfilename('C: est', 'test.pdf');

But It is throwing below error:

ORA-06550: line 9, column 42:
PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got FILE
ORA-06550: line 9, column 1:
PL/SQL: SQL Statement ignored

View 2 Replies View Related

PL/SQL :: Inserting Records From Sql Loader

Mar 7, 2013

I am trying to insert record from sql loader but this is inserting only single row.

View 3 Replies View Related

Inserting Data To Tables On Another User?

Mar 13, 2007

how can I insert data into tables on another user. They both are in the same table-space.

View 1 Replies View Related

Inserting Restriction By Existing Data

Nov 8, 2010

In my organization, I have a table and in that there is a column named "code".I want to restrict some insertion to that particular column. suppose that code column values are 12 and 1245 then i cant insert the value 12,1245, 1 ,124 and so on but i can insert 2 ,123,15,12456 and so on.

that means the new values should not be any substring of the existing data from left. making that column primary key and then I had a logic to compare the existing value which are longer than the new value and then to perform this.But dont know how to make it happen correctly.

View 2 Replies View Related







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