| Moving Table Contents and Maintaing PK Referential Integrity. [message #135296] |
Tue, 30 August 2005 16:40  |
chrismarino_nj
Messages: 1
Registered: August 2005
Location: NJ
|
Junior Member |
|
|
I am using oracle 9i
I am trying to move the contents from one table in a DB to another table with a different name and different Row Names, everything else is the same. I am able to move the data however the PK is not preserverd. The target DB assigns new values in the PK column. For instance the table in my Source DB has 301,250,256 for the first 3 PK rows, the table in my target db has 1,2,3
I am totaly new and I do everything through the EM GUI. I was using SQL DTS to move the data
|
|
|
|
| Re: Moving Table Contents and Maintaing PK Referential Integrity. [message #135391 is a reply to message #135296] |
Wed, 31 August 2005 07:29  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Is there any sequence involved?
YOu have to provide more details.
I have not used SqlDTS. So i really have no idea what it does in background. I will look into it.
It also depends on how the tool 'moves' the data.
IN this kind of sql simulation,
ONly the data is copied. all integrity constraints/indexes are Not pushed along with.
You have to do it seperately
--
-- create a pk
--
1* alter table emp add constraint pk1 primary key (empno)
scott@9i > /
Table altered.
scott@9i > desc emp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
scott@9i > select constraint_name,table_name,constraint_type from user_constraints;
CONSTRAINT_NAME TABLE_NAME C
------------------------------ ------------------------------ -
PK1 EMP P
scott@9i > select table_name, tablespace_name from user_Tables where table_namE='EMP';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMP USERS
1* create table another_emp as select empno eno,job jobname,mgr magname from emp
scott@9i > /
Table created.
scott@9i > select constraint_name,table_name,constraint_type from user_constraints;
CONSTRAINT_NAME TABLE_NAME C
------------------------------ ------------------------------ -
PK1 EMP P
scott@9i > desc another_emp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ENO NUMBER
JOBNAME VARCHAR2(9)
MAGNAME NUMBER(4)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
