0

I am trying to move a table which resides in a certain schema to a different schema with the same table name. I have tried the following but they do not work:

rename <OLD_SCHEMA_NAME>.<TABLE_NAME> TO <NEW_SCHEMA_NAME>.<TABLE_NAME>;

The error that appears is:

SQL Error [42000]: invalid identifier chain for new name [line 1, column 100] (Session: 1722923178259251200) and

ALTER TABLE <OLD_SCHEMA_NAME>.<TABLE_NAME> RENAME <NEW_SCHEMA_NAME>.<TABLE_NAME>;

The error that appears is:

SQL Error [42000]: syntax error, unexpected IDENTIFIER_PART_, expecting COLUMN_ or CONSTRAINT_ [line 1, column 62] (Session: 1722923178259251200)

Many Thanks!

Mohamed Gamal
  • 11
  • 4

1 Answers1

0

According to Exasol documentation there is no way to move table between schemas using RENAME statement:

Schema objects cannot be shifted to another schema with the RENAME statement. For example, 'RENAME TABLE s1.t1 TO s2.t2' is not allowed.

I would move the table this way:

create table <NEW_SCHEMA_NAME>.<TABLE_NAME>
like <OLD_SCHEMA_NAME>.<TABLE_NAME>
including defaults
including identity
including comments;

insert into <NEW_SCHEMA_NAME>.<TABLE_NAME>
select *
from <OLD_SCHEMA_NAME>.<TABLE_NAME>;

drop table <OLD_SCHEMA_NAME>.<TABLE_NAME>;
GriGrim
  • 2,891
  • 1
  • 19
  • 33