| copy one table to another table [message #638476] |
Fri, 12 June 2015 06:44  |
 |
arun888
Messages: 100
Registered: June 2015
Location: INDIA
|
Senior Member |
|
|
hi all, it would be great if anyone can help me out in this issue.
There are 30,000 table present in the schema. where the user will passing the source and destination table as input.
i am wrriting a shell script to copy the one table data to the another table by overwritten.
could you please provide the oracle query to copy one table to another table.
|
|
|
|
|
|
| Re: copy one table to another table [message #638479 is a reply to message #638476] |
Fri, 12 June 2015 06:48  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
assuming the ddl in both tables is identical:
insert into tablex select * from tabley;
HOWEVER:
What on earth are you doing with 30000 tables and why are you copying data from one table to another?
|
|
|
|
|
|
|
|
| Re: copy one table to another table [message #638482 is a reply to message #638480] |
Fri, 12 June 2015 06:58 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
All you have done is reiterate what you originally asked. You provided NO additional information. The point behind my question is
1. It sounds like a ridiculous request. Copying an entire table into another table does nothinbg but duplicate data. What's the point of that?
2. 30000 sounds like a MASSIVE number of tables, it sounds like there have been (and continue to be) some very poor design decisions happening there.
Also, you didn't answer BlackSwan's very pertinent question
"What to do when the number of columns or the column datatypes do not match?"
[Updated on: Fri, 12 June 2015 06:59] Report message to a moderator |
|
|
|
| Re: copy one table to another table [message #638483 is a reply to message #638480] |
Fri, 12 June 2015 06:59 |
|
arun888
Messages: 100
Registered: June 2015
Location: INDIA
|
Senior Member |
|
|
tablex -> ram,kk,gg [source table]
tabley -> ram,ll,oo,mm [destination table]
insert into tabley select * from tablex;
In this case, after the above query. I believe the tabley table will be overwritten ?
will the result be :
tablex -> ram,kk,gg
tabley -> ram,ll,oo
or
tabley -> ram,ll,oo,mm,kk,gg
|
|
|
|
|
|
|
|
|
|
|
|
| Re: copy one table to another table [message #638488 is a reply to message #638483] |
Fri, 12 June 2015 07:03 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
orclz>
orclz> conn scott/tiger
Connected.
orclz> desc emp;
Name Null? Type
----------------------------------------------------------- -------- -----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
orclz> desc dept;
Name Null? Type
----------------------------------------------------------- -------- -----------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
orclz> insert into emp select * from dept;
insert into emp select * from dept
*
ERROR at line 1:
ORA-00947: not enough values
orclz> |
|
|
|
| Re: copy one table to another table [message #638489 is a reply to message #638485] |
Fri, 12 June 2015 07:04 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
1. Once again, what EXACTLY do you mean by 'overwritten'. Simply inserting data into a table will not overwrite that table (whatever 'overwrite' means, unless it meanst 'insert into', in which case, just say 'insert into') Try not to make up your own terms.
2. Root user? Are you saying that you have a user called root? Or are you implying that the tables are owned by sys?
|
|
|
|
|
|
| Re: copy one table to another table [message #638491 is a reply to message #638490] |
Fri, 12 June 2015 07:20 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Quote:
If tablex is copied to tabley. tabley should be same as tablex. previous data of tablex can be dropped.
As Blackswan mentioned above. Your assumption is incorrect. the data will be ADDED/APPENDED to the existing data.
Quote:its owned by sys. Do NOT do this! it is an horrendous idea Michel can no doubt link you to one of his lists as to why it is a horrible idea. Over to you Mr C  )
ETA:
Actually, to save Michel having to do it:
Quote:Michel Cadot writes (on far too many occassions  )
- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.
NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.
ETA2: Some more detail:
http://www.orafaq.com/forum/t/164126/0/
[Updated on: Fri, 12 June 2015 07:36] Report message to a moderator |
|
|
|
| Re: copy one table to another table [message #638492 is a reply to message #638491] |
Fri, 12 June 2015 07:24 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
All of these discussion miss (apart from an initial comment on) the fact that this process that you intend seems to be pretty awful. Can you explain exactly what business scenario is happening here.
|
|
|
|
|
|
|
|
|
|
| Re: copy one table to another table [message #638498 is a reply to message #638497] |
Fri, 12 June 2015 08:34 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
arun888 wrote on Fri, 12 June 2015 14:28I am writiting a tool for the users. To copy the previous data to the current table. Manually some times users will copying the data manually like this.
So do you then drop the 'previous data' table? How many tables are there with identical structure? Why does data get loaded into one table and then bounced into another table? Why not loaded directly into the 'final destination' missing out the seemingly unneccesary 'staging' table.
Has this set of tables actually been built yet?
|
|
|
|
|
|
| Re: copy one table to another table [message #638501 is a reply to message #638498] |
Fri, 12 June 2015 09:15 |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
Correct me if I am wrong, but I suspect the reason to copy these tables is to populate some king of
test database? If the test DB is a clone of the source DB, copying the data from one db to another
may not be enough, what about triggers that would need to be disabled? or sequences? or FK
constraints?
I faced similar issue where a set of "common" tables needed to constantly be "synchronized" with prod in the test and QA database(s), and I wrote a script like the attached.
Script is based on a source (organization external) table with the list of candidate tables and in the following format (you could create permanent table):
#:############################################################
#: ResyncTab.dat
#:
#: List of tables and corresponding sequences to be re-sync'd
#:
#:------------------------------------------------------------
#: Change the first field to '1' to select table(s) to synch.
#:------------------------------------------------------------
#:FLAG:TABLE:SEQUENCE_NAME:
0:'ACTIVITY_CODES':'ACTIVITY_CODES_SEQ':
0:'APPLICATIONS':'APP_ID_SEQ':
0:'BARCODES'::
1:'CODE_TABLE':'CODE_TABLE_SEQ':
1:'CODE_TYPES':'CODE_TYPES_SEQ':
0:'CONTACT_SUB_TYPE':'CONTACT_SUB_TYPE_SEQ':
0:'CONTACT_TYPE':'CONTACT_TYPE_SEQ':
0:'COVER_COMMENTS'::
0:'EDOCUMENT'::
0:'EQUESTIONS'::
# - - - e t c - - -
See script attached.
[Updated on: Fri, 12 June 2015 09:27] Report message to a moderator |
|
|
|
| Re: copy one table to another table [message #638507 is a reply to message #638501] |
Fri, 12 June 2015 10:06 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
LKBrwn_DBA wrote on Fri, 12 June 2015 19:45Correct me if I am wrong, but I suspect the reason to copy these tables is to populate some king of
test database?
After reading the entire thread, I didn't find any clue about different databases being involved. In fact, OP is trying to do something silly in the same schema as I understand.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
