Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say I have the following database/schema setup with the following tables:

    SCHEMA_1 (default):
        TABLE_1

    SCHEMA_2:
        TABLE_2

How do I insert rows in another schema's table? The following does not seem to work:

    INSERT INTO TABLE_2 (...) VALUES(...)

How is it possible to insert rows in another another schema?

share|improve this question
    
SELECT ... FROM schema_2.TABLE_2 , INSERT INTO schema_2.TABLE_2 –  ypercube Apr 10 '12 at 11:29

2 Answers 2

up vote 2 down vote accepted

Fully qualify the table name:

insert into SCHEMA_2.TABLE_2 values( ... );
share|improve this answer
    
I tried that but I get the error message that "SCHEMA_1.SCHEMA_2.TABLE_2" does not exist. –  Andy Apr 10 '12 at 11:35
    
SCHEMA_1.SCHEMA_2.TABLE_2 ? What exactly did you type to do the insert? –  Phil Apr 10 '12 at 11:36
    
Sorry now I understand, I used ` around the table: INSERT INTO SCHEMA_2.TABLE_2 (...) VALUES(...). Did not know that it made a difference. –  Andy Apr 10 '12 at 17:17
    
Note that for some reason the `-sign is not shown around SCHEME_2.TABLE_2 in my above comment. –  Andy Apr 10 '12 at 17:27
    
@Andy: You used INSERT INTO `schema2.table2` VALUES .... You should either remove those (as in Phil's answer) or: INSERT INTO `schema2`.`table2` VALUES ... (if necessary to use backquotes). The dot . should not be inside the quotes :) –  ypercube Apr 10 '12 at 17:57

It should be fully qualified table name means DatabaseName.TableName In your case it will be

Insert into schema1.table1 (select * from schema2.table2);

This will insert all rows from schema2.table2 to schema1.table1

share|improve this answer
    
Parentheses not needed, but +1 anyway !!! –  RolandoMySQLDBA Apr 10 '12 at 15:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.