October 21st, 2003, 10:27 AM
am trying to migrate table A to table B from same instance but different schema. if the table have different structure and some different column is it possible to do the migration of data? for example coulmn 1 in table A has different fields then coulmn 1 in table B.
October 21st, 2003, 12:00 PM
You mean by import utility of oracle? If yes, then you cannot do so. But if the datatype is different and compatible then you can import it using IGNORE=Y option with your import command. Remember column name should be identical.
How many tables need to be imported? If only 5-10, then you can load the data between schemas by using INSERT command, no matter your columns names are different but dtattype should be identical or compatible. For example you have one table 'Employees' that has different structure then most popular table 'EMP', then your query would be look like:
INSERT INTO Employees (SELECT empno,ename,sal,dept FROM emp);
October 22nd, 2003, 06:57 PM
As mehak said, the tables are in different schemas.
So, one way to take care of it is by specifying the schema name as in:
INSERT INTO Employees (SELECT schema_name.empno,ename,sal,dept FROM emp);
Another way to do it is via synonyms.
The schema from which you run your INSERT statement needs a select privilege on the other schema.
October 23rd, 2003, 08:51 AM
Thanks dan to indicate my mistake.
Sorry mehak, my query would be look like that:
INSERT INTO Employees (SELECT empno,ename,sal,dept FROM schema_name.emp);