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
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
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
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
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
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
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
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
Jul 26, 2011
shall we stop inserting data into a table before it inserting using Trigger?
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Mar 7, 2013
I am trying to insert record from sql loader but this is inserting only single row.
View 3 Replies
View Related
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
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
