4

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?

  • SELECT ... FROM schema_2.TABLE_2 , INSERT INTO schema_2.TABLE_2 – ypercubeᵀᴹ Apr 10 '12 at 11:29
4

Fully qualify the table name:

insert into SCHEMA_2.TABLE_2 values( ... );
  • 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
2

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

  • Parentheses not needed, but +1 anyway !!! – RolandoMySQLDBA Apr 10 '12 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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