SQL & PL/SQL :: Copy Value In Column To Another Table?

May 21, 2006

Is it possible to copy a value in a column from a table into another column in another table using the select statement.

Example: I want to copy the arrival date from the guest table to date_charge in the charges table.

CREATE TABLE GUEST
(ROOM_NUMBER VARCHAR2(2),
DATE_ARRIVAL DATE DEFAULT SYSDATE NOT NULL);
CREATE TABLE CHARGES
(ROOM_NUMBER VARCHAR2(2),
DATE_CHARGE DATE default Sysdate);

[code]....

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Copy Single Column From One Table To Another?

May 27, 2010

How can i copy a single column from one table to another. Table 1 has a column with data in it, table 2 has the column but is empty, i want to copy data for a single column from table1 to table 2. By the way these table have multiple columns.

View 8 Replies View Related

Server Utilities :: Copy Table With NCLOB Column Into Another Database (10g To 11g)?

Dec 4, 2012

I need to copy one table which is having NCLOB column into our new DB(11g) from old DB (10g). As this table having huge records (1 crore) it is difficult to use direct insert statement (INSERT INTO ... SELECT * FROM @remotedb). I don't have DBA privilege to use loader.

View 3 Replies View Related

SQL & PL/SQL :: BLOB Datatype - Copy From One Column To Another

Mar 11, 2011

I have two tables with BLOB datatype. I am trying to copy from one column to another column , i am getting errors

CREATE OR REPLACE PROCEDURE update_blob_lob_copy(
v_id in integer,
v_string in varchar
)
as
auditlob lobdemoaudit.theblob%type;
sourcelob lobdemo.THEBLOB%type;
[code]...

View 3 Replies View Related

How To Copy Set Of Rows N Times And Append Them With Different Value For A Column

May 27, 2011

Im trying to replicate a set of rows multiple times to create large volume. I am trying by For Loop, but got confused how to pass the parameters in any cursor i declare.

View 8 Replies View Related

SQL & PL/SQL :: Copy Data From Table A To B?

Apr 30, 2010

i could copy data from tableA to tableB with the scenario below

1) Currently we have no unique key / primary key defined on both of this tables, due to the nature of data, so insert/update will not work
2) We cant truncate/insert as user will be accessing this tables when the copying process takes place, so we dont want to end up having a scenario where the table does not contain data at a certain time

Will materialize view full refresh work in refreshing a table so we could avoid the problem faced in point 1 & 2.

View 7 Replies View Related

SQL & PL/SQL :: Copy Data From One Table To Another

Feb 11, 2013

I have two tables namely PERSON and WIFE. I want to make WIFE's data available in PERSON table while keeping entries of WIFE maintained and at the same time adding some the values of PERSON against the data of wife.

PERSON Table
PK NAME ADDRESS IS_MARRIED
1 John ab city Y
2 Varvatos cd town N
3 Smith ef town Y
4 Henry gh city Y
5 Lynda gh city Y

WIFE table

PK PERSON_ID (FK) NAME
1 1 Alice
2 3 Rosy
3 4 Lynda

Now i want to copy data of WIFE table into PERSON table like this PERSON table

PK NAME ADDRESS IS_MARRIED
1 John ab city Y
2 Varvatos cd town N
3 Smith ef town Y
4 Henry gh city Y
5 Lynda gh city Y
6 Alice ab city Y
7 Rosy ef town Y

As in the given example you might have noticed that ADDRESS of wife is same as of her spouse and same goes for IS_MARRIED column. Moreover, the PK is also not duplicated. How to go about this?

Another important factor is Lynda already exits in PERSON table, therefore, i certainly don't want to duplicate her entry.

View 8 Replies View Related

SQL & PL/SQL :: Trigger To Copy Data From One To Another Table

Jun 28, 2010

1. I am inserting a value to a table.through uploading the csv file to the DB table using apex.

2. After the data get inserted into the table(parent),my data should be copied to the another table with some conditions .

3. Is it possible to write a trigger to insert statement from selecting the values from another table.

View 5 Replies View Related

SQL & PL/SQL :: Copy Data And Constraints From One Table To Another

Dec 20, 2011

how to copy data and constraints from one table to another table

View 8 Replies View Related

How To Create Exact Copy Of A Table

Nov 24, 2012

I must admit my "google skills" have failed me and it is possible that I might be missing something obvious here but allow me to explain.

If I have a table that is populated/accessed by a typical OLTP application such that over time the data in the table may become "fragmented".

I know that is a very controversial term I used but that is not the question here. A simple way to describe the state of the table might be that the table has always been populated with lots of small INSERTs, modified with lots of small UPDATEs and data has been deleted in small DELETEs.

This has meant that the data is neither closely stored in data blocks nor is in any particular order (so there are some empty blocks under HWM).

Now my question is how do I create exact copy of this table and its indexes AS THEY EXIST AT THE MOMENT that includes

1) its data
2) its constraints/indexes etc.
3) its storage parameters
4) data stored in EXACTLY same manner as in original table.

To avoid complications, the table in question is just a normal heap table without any partitioning involved. While

CREATE TABLE AS SELECT (or CREATE TABLE followed by INSERT) will take care of points (1) to (3) above, it will not achieve point (4) above.

View 11 Replies View Related

Copy Data From One Field (varchar) To Another Within A Table

Oct 5, 2010

i have a table COLOR having alphanumeric fields i.e Color Description & Color Ref. i want to copy the values from Color Description to Color Ref.

what will the select statement for this?

View 9 Replies View Related

Oracle Triggers - Copy Data From One Table To Other

Jul 9, 2008

Triggers for oracle database. What I am trying to do is copy original data from one table to another table prior to an update, insert, or delete occurring. Basically we are trying to keep a transactional history, with out having to restore the data. Here is what I have created to date, however it is not executing consistently.

CREATE OR REPLACE TRIGGER DATA_COPY BEFORE
INSERT
OR DELETE
OR UPDATE ON "DB1"."TABLE_1"
[code].......

View 10 Replies View Related

SQL & PL/SQL :: Copy Data Of One Partition Completely To Another Table

Nov 16, 2010

I have 2 tables with same no of column and range partition based on date.At the end of month i want to copy the data of one table to another table.Instead of copy the data i want to copy the data of one partition completely to another table partition ..

View 2 Replies View Related

SQL & PL/SQL :: Copy Into Same Table / Recursive Rename Of Duplicates

May 11, 2011

In MS Windows, if I copy a file and paste it into the same folder, I get a copy with the text 'Copy of' in front of the file name. If I paste it again, I get another copy with a different version number.

E.g.
sqlnet.log
Copy of sqlnet.log
Copy (1) of sqlnet.log
Copy (2) of sqlnet.log

I was wondering if I could copy existing rows into a table and do the same thing?

So, for example if I had this table:

create table tst_srch (srch_is varchar(100), user_name varchar(100), srch_name varchar(100));

insert into tst_srch values (1,'USER1','SRCHA');
insert into tst_srch values (2,'USER1','SRCHB');
insert into tst_srch values (3,'USER1','SRCHC');
insert into tst_srch values (4,'USER1','SRCHD');
insert into tst_srch values (5,'USER2','SRCHC');
insert into tst_srch values (6,'USER2','SRCHD');
insert into tst_srch values (7,'USER2','SRCHD_1');

Could I write a procedure like copy_searches('USER1','USER2') that would copy all USER1's searches to USER2 - including renaming any duplicates.

So it would create these new rows:

8,USER2,SRCHA
9,USER2,SRCHB
10,USER2,SRCHC_1
11,USER2,SRCHD_2

I've looked at various insert statements, merge and match statements and exception handling in procedures .

View 6 Replies View Related

Automated Script To Copy Data From One Table To Another Every Night?

Feb 27, 2012

i have a table with 15 coulums and containing millions of rows which is being updated everyday.Now i have created a similar report table with only the coulums i need to report on from the main table.what plsql script or if there is any better alternative do i need to write to copy the data from the coulums i need from the main table to the new report table. the new report table will be be updated every 01:00am with the data coming from the main table and the update is automated.

View 3 Replies View Related

Create A Copy Of Missions Table Called AM_X_442_2

Jan 22, 2009

My solution to the following question update 10 rows. It should update only 6 records. Create a copy of the missions table called AM_X_442_2. For missions on this table that meet the following criteria:

1. within the 10 most recent missions
2. length of the first word of the mission code_name exceeds 7 characters

Change the security level to the highest security level found in missions of the same type.

UPDATE
AM_X_442_2 am
SET
am.SECURITY_LEVEL =
(
SELECT
MAX(amx.SECURITY_LEVEL)
FROM
AM_X_442_2 am
INNER JOIN
AM_X_442_2 amx
[code]....

View 2 Replies View Related

SQL & PL/SQL :: How To Copy Constraints (primary / Foreign Key / Indexes) To A Table

Mar 11, 2011

I have created table as below

create table emp_temp as select * from emp;

the table is created, but the constraints are not copied. Is there any way to copy all the constraints.

View 3 Replies View Related

Forms :: How To Copy Values From One Table To Another Using When-Button-Pressed Trigger

Feb 14, 2013

I have two tables one is

1)create table abc(c_name varchar2(10),c_number number(6),c_loc varchar2(8)); --having values
2)create table temp(sname varchar2(10),sid number(4),address varchar2(10));---no having

and my question is how to insert a values into temp using when-button-pressed trigger based on abc table in oracle form.

View 16 Replies View Related

SQL & PL/SQL :: Copy Supplemental Login Information From Renamed Table To New Tables?

Oct 19, 2010

I renamed 100 tables and recreated them, now I need to copy supplemental login information from renamed table to new tables. Environment is oracle 10G.

OS - Solaris

View 9 Replies View Related

Server Utilities :: Copy Table With Special Char Between Oracle 8 And 10?

Nov 17, 2010

I have to copy a table from oracle 8 to oracle 10
- dblink don't work between 8 - 10
- can I dump table ?
- when I try to use sqlload via TXT file, I have error messages due to a column containing 'special' caracters (
,..) and line too long (splitted in several rows)

how can I do ?

View 1 Replies View Related

Table Stats Copy Amount Sub-partition To Save Load On System - Error?

Oct 10, 2012

i have a large OLTP database and we are doing table stats copy amount subpartition to save the load on system. while doing an copy default subpartition stats: I see the following error:

SQL> exec DBMS_STATS.COPY_TABLE_STATS('cusms','STATUS_HIST','P_VDEF_10_2012_S100','P_VDEF13_10_2012_S100',force=>true);
BEGIN DBMS_STATS.COPY_TABLE_STATS('cusms','STATUS_HIST','P_VDEF_10_2012_S100','P_VDEF13_10_2012_S100',force=>true); END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

View 2 Replies View Related

Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention

May 15, 2011

i have two questions.

(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'

(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.

View 3 Replies View Related

Performance Tuning :: Update Currency Column Of One Table Using Other Table Currency Column?

May 11, 2012

I am trying to update currency column of one table using the currency column of other table using the following sql code.

update ODS.SO_ITEM OSI
set CURRENCY__CODE=(select currency__code from sa_sales.SO_ITEM SSI where SSI.ID=OSI.ID)

This update is taking taking a lot of time and is never ending.

should i create index on source table (SA_SALES.SO_ITEM) or on target table (ODS.SO_ITEM) ?

View 7 Replies View Related

SQL & PL/SQL :: Add New Column To Table Between Column List?

Jun 16, 2010

How to add column 'col_new' in below table after the column 'col1'?

Table name: ofq1
table structure.
columnname datatype
------- -------
COL1VARCHAR2(20 BYTE)
COL2NUMBER
COL3DATE
COL4DATE

View 8 Replies View Related

All Columns Of A Table Are Part Of Primary Key Column Of That Table?

Feb 22, 2011

if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).

should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.

there is a drawback if a most of the tables of a database contain composite primary keys?

View 3 Replies View Related

SQL & PL/SQL :: How To Join A Table Column Names With Data From Other Table

Jul 18, 2012

I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.

CREATE TABLE SALESHIST
(
PRODUCT VARCHAR2(30 BYTE),
Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),
Q4 VARCHAR2(30)
)
[code]......

View 6 Replies View Related

PL/SQL :: How To Get Column And Table Name By Given Column Value

Sep 28, 2012

I have a requirement as to find all the tables and columns containing keyword "SELECT" in my database. I followed forum

Re: How to find the column name and table name with a value

But when i run the below query on 11.0.2g Oracle Database

select table_name,
column_name,
:search_string search_string,
result
from (select column_name,

[Code]...

I get

ORA-29913: error in executing contains callout ORA-20000: Oracle Text error: 29913. 00000 - "error in executing %s callout"

*Cause:    The execution of the specified callout caused an error.
*Action:   Examine the error messages take appropriate action.

View 5 Replies View Related

SQL & PL/SQL :: Use Value Of One Column In Other In Same Table

Aug 17, 2011

I have two table country and state.Columns are given below

SQL> DESC Country

Name Null? Type
----------------- -------- --------------
PRIMARY_KEY NOT NULL NUMBER
DISPLAY_ORDER NOT NULL NUMBER
NAME NOT NULL VARCHAR2(50)
TREE_POSITION NOT NULL VARCHAR2(250)
PARENT_ID NUMBER

SQL> desc state
Name Null? Type
----------------- -------- -------------------------
PRIMARY_KEY NOT NULL NUMBER
DISPLAY_ORDER NOT NULL NUMBER
NAME NOT NULL VARCHAR2(50 CHAR)
VALUE NOT NULL VARCHAR2(50 CHAR)
TREE_POSITION NOT NULL VARCHAR2(250 CHAR)
PARENT_ID NUMBER

Now I have to insert the values in the state table taking from the country table.

EXAMPLE :

INSERT INTO STATE (PRIMARY_KEY, DISPLAY_ORDER, NAME, VALUE, PARENT_ID, TREE_POSITION) VALUES (251, 1, 'Alaska', 'AK', 233, 'STAT_USUS_AKAK');

but here the tree position is static it can be anything at the client side so I want to make it dynamic. So to make dynamic what I have used is mentioned below:

INSERT INTO STATE
(PRIMARY_KEY,DISPLAY_ORDER,NAME, VALUE,
PARENT_ID,
TREE_POSITION)
VALUES

[code].....

Now I want that Quote: select tree_position from L_STATE_ITEM where name ='United States')||'_ALAL' which i have used in the state table to put value in the tree_position, I want to make the other half part after the concatenation operator "_ALAL" also dynamic.

So I was just wondering If I can pic that value from the "VALUE COLUMN" of the same table.

View 3 Replies View Related

How To Add New Column At Specified Location In A Table

Dec 29, 2010

How can I add new column at the specified location in a oracle table? I am having a flat file from which data is inserted to the table..But initially I don't need that column so I m not having this column in the table but if further i need that column at the particular location then how can I do this..?

View 1 Replies View Related

Update New Column Within Same Table

Oct 22, 2010

I have a table ITEM as under;

ARTICLE VRTCODENAMESTATUSNEW_NAME
4KABAN.NM.12.21121143SU19I
4KABAN.NM.12.21564101SU23I
4KABAN.NM.24,5.22491015SR08I
4KABAN.NM.24,5.23181038SR22I
4KABAN.NM.24,5.23491015SR08I
4KABAN.NM.24,5.24121161SR26I

how to update the filed from NAME to NEW_NAME, I need following output.

ARTICLE VRTCODENAMESTATUSNEW_NAME
4KABAN.NM.12.21121143SU19ISU19
4KABAN.NM.12.21564101SU23ISU23
4KABAN.NM.24,5.22491015SR08ISR08
4KABAN.NM.24,5.23181038SR22ISR22
4KABAN.NM.24,5.23491015SR08ISR08
4KABAN.NM.24,5.24121161SR26ISR26

View 2 Replies View Related







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