January 31st, 2014, 03:18 AM
Copy table data to other table
Hello, is there any short query that allows copy table data to other table? Even when for example table1 has different schema then table2, so i dont have need to use this - INSERT INTO table1 all columns SELECT all columns + new values or columnts that arent in table2 but are in tabl1 FROM table2 etc. Like i have in table1 like about 20 columns but in table2 there are only 14.
i have 4 history tables and those same 4 history hqt tables, like HST_SRV_CASES and HST_SRV_CASES_HQT. In HST_SRV_CASES there are more columns than in HST_SRV_CASES_HQT, because HST_SRV_CASES_HQT isnt used in long time and i need copy data from that table to usual history table which is being used right now and then delete all data from hqt table when finished copying all.
So is there any short query to do this?
Thank you for answers
February 26th, 2014, 02:34 PM
NO, there is not,
Originally Posted by xWarriors
February 28th, 2014, 10:10 AM
Well, perhaps there is a chance:
Originally Posted by LKBrwn_DBA
1) Dump the source table to a delimited file.
2) Create an sql loader control file to map that dump file to the target table
3) Execute sql loader with "append" option to load the data.
July 18th, 2014, 03:14 PM
You can use schema name just before the table name to copty data from one table to another.
For example you want to copy data from table_main which is in the HR schema to table_temporary which is in DEV schema. First connect to DEV schema and try the following code:
Another thing< I just remember you can copy data even from different oracle database on different computer also as long as you have access to that computer and it is also oracle database; to do so, use database link. fo to oracle website to find more detail.
INSERT INTO table_temporary (SELECT * FROM hr.table_main);
October 9th, 2014, 05:17 PM
If I understood the question correctly, I think there are couple of way to do it -
1. Using CTAS Method. Here is a sample code from our system -
create table MYSCHEMA.SHIPMENT0814 parallel (degree 4) nologging as select /*+parallel(source 4) */ * from SOURCE.SHIPMENT where insert_date > trunc(to_date('01-MAY-12')) AND domain_name IN (SELECT VALUE FROM z_accrual_parameters WHERE parameter_set_status = 'ACTIVE' AND parameter_name = 'DOMAIN_NAME') AND domain_name != 'CLIENTDATA/A002';
then you can add some empty (or default values) to the columns similar to the new table and copy the table across so the #columns match.
2. Using the specific named columns, for example -
Insert Into MYSCHEMA.USER_MENU_ACCESS
From SOURCE.User_Menu_Access_new Where Domain_Name = 'CLIENTDATA/A008' ;