CODEWITHSUNDEEP
sqloracle-database
mahdi
mahdi

Reputation: 1

Insert table from one database into another database

I want to copy a table to another database server in SQL developer. I use this method to solve this problem.

INSERT INTO mahdi-jiradb..empier SELECT * FROM tamindb..users;

but i get an error:

Error starting at line : 8 in command -
INSERT INTO mahdi-jiradb..empier
SELECT * FROM tamindb..users
Error at Command Line : 8 Column : 18
Error report -
SQL Error: ORA-00926: missing VALUES keyword
00926. 00000 -  "missing VALUES keyword"
*Cause:    
*Action:

What should i do?

Upvotes: 0

Views: 221

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

As you commented, it looks as if you use Oracle.

In that case, schema name can't contain a "minus" sign (mahdi-jiradb) so that's probably underline (mahdi_jiradb) instead.

SQL> create user mahdi-jiradb identified by test;
create user mahdi-jiradb identified by test
                 *
ERROR at line 1:
ORA-00922: missing or invalid option

Unless, of course, you enclosed username into double quotes (but now you have to use it always, everywhere):

SQL> create user "mahdi-jiradb" identified by test;

User created.

Presuming that that's not the case, schema name is wrong. Check it.

Also, use one dot to separate schema name from table name.

Something like this might work:

INSERT INTO mahdi_jiradb.empier SELECT * FROM tamindb.users;

presuming that both tables share the same description. That's why it is better to explicitly name all columns involved, e.g.

INSERT INTO mahdi_jiradb.empier (user_id, user_name)
  SELECT uid, uname FROM tamindb.users;

Upvotes: 1

Related Questions