Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    20

    Unanswered: sql query problem

    dear friends,

    we have 2 different type of host strings but same user name and password like

    1) john/john@prw
    2) john/john@prw1

    problem is :- two host string is having same tables .
    prw string is having with records but
    prw1 string is having only database struture (like table only) does't have records.

    requirements is :-

    i want to copy records from host string prw to prw1 ?? but i don't want to delete the table??

    wat's the syntax??

    please let me know

    thankyou,
    john.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You could try with the IMPORT / EXPORT utilities, for example:
    EXP john/john@prw FILE=prw.dmp
    IMP john/john@prw1 FILE=prw.dmp IGNORE=Y

    INGORE identifier will import only data and preven errors due to existing tables in the PRW1 schema.

    Using INSERT INTO ... SELECT FROM could work, but - if you have many tables, you'd need to write some kind of automatized procedure (who wants to write hundreds of statements, even with the copy/paste?); I guess imp/exp is better solution.

  3. #3
    Join Date
    Mar 2004
    Posts
    20

    for littlefoot

    dear littlefoot,

    thanks for your idea,
    but what i am try to say's i want copy only one table records . if i do exp/imp that whole dump will be imported , it's very large size dump . i don't neet that

    i just want to copy only one table records.. it's there alternative solution for u have !!

    thanks

    john

    Quote Originally Posted by Littlefoot
    You could try with the IMPORT / EXPORT utilities, for example:
    EXP john/john@prw FILE=prw.dmp
    IMP john/john@prw1 FILE=prw.dmp IGNORE=Y

    INGORE identifier will import only data and preven errors due to existing tables in the PRW1 schema.

    Using INSERT INTO ... SELECT FROM could work, but - if you have many tables, you'd need to write some kind of automatized procedure (who wants to write hundreds of statements, even with the copy/paste?); I guess imp/exp is better solution.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Of course; imp/exp only one table

    EXP john/john@prw TABLES=(your_table_name) FILE=prw.dmp
    IMP john/john@prw1 FILE=prw.dmp IGNORE=Y

    What else you could do (when connected to john@prw1):
    INSERT INTO john.your_table_name
    SELECT * FROM john.your_table_name@prw;

    It requires you to GRANT SELECT on prw.john.your_table_name to prw1.john.

    Or, vice versa, GRANT INSERT to the table in john@prw1 schema to be able to insert data when connected to john@prw.
    Last edited by Littlefoot; 03-10-05 at 04:36.

  5. #5
    Join Date
    Mar 2004
    Posts
    20

    for littlefoot

    Mr.littlefoot,
    i was tried this snytax

    INSERT INTO john.your_table_name
    SELECT * FROM john.your_table_name@prw;

    but the error should comes here,

    ERROR at line 1:
    ORA-00926: missing VALUES keyword

    wat iam going to do ???

    bye
    leo


    Quote Originally Posted by Littlefoot
    Of course; imp/exp only one table

    EXP john/john@prw TABLES=(your_table_name) FILE=prw.dmp
    IMP john/john@prw1 FILE=prw.dmp IGNORE=Y

    What else you could do (when connected to john@prw1):
    INSERT INTO john.your_table_name
    SELECT * FROM john.your_table_name@prw;

    It requires you to GRANT SELECT on prw.john.your_table_name to prw1.john.

    Or, vice versa, GRANT INSERT to the table in john@prw1 schema to be able to insert data when connected to john@prw.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is how it should be done (I've already created an empty 'emp' table in 'pelen' schema);
    Code:
    SQL> connect scott/tiger@ora8i
    Connected.
    SQL> grant select on emp to pelen;
    
    Grant succeeded.
    
    SQL> connect pelen@ora8i
    Enter password:
    Connected.
    SQL> select * from emp;
    
    no rows selected
    
    SQL> insert into emp select * from scott.emp@ora8i;
    
    12 rows created.
    
    SQL> select empno, ename from emp;
    
         EMPNO ENAME
    ---------- ----------
          7369 SMITH
          7499 ALLEN
          7521 WARD
          7566 JONES
          7654 MARTIN
          7698 BLAKE
          7782 CLARK
          7839 KING
          7844 TURNER
          7900 JAMES
          7902 FORD
          7934 MILLER
    
    12 rows selected.
    
    SQL>
    Compare this with your code and find out what went wrong.

    P.S. If you miss the VALUE keyword, you've probably used something like this:
    INSERT INTO table_name (col1, col2, ...)
    VALUE <- this is where VALUE should be used.
    (value1, value2, ...)
    Last edited by Littlefoot; 03-10-05 at 08:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •