[
https://issues.apache.org/jira/browse/DERBY-6205?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13641859#comment-13641859
]
Rick Hillegas commented on DERBY-6205:
--------------------------------------
There may be a more general need here to move any kind of schema object from
one schema to another schema. Of course, you can always create a new object,
migrate the data, and drop the old object. But that is not efficient for large
tables. It is also inconvenient for any schema object which has dependent
objects. E.g., a function which is referenced by views, triggers, or generation
clauses.
Moving an object from one schema to another may change the ownership of the
object. That may have cascading GRANT/REVOKE implications.
For the original use case (moving a table to another schema), I find the
following SQL Server syntax (see
http://stackoverflow.com/questions/1149159/how-do-i-move-a-table-into-a-schema-in-t-sql):
ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;
The following syntax is suggested by
http://www.pythian.com/blog/oracle-how-to-move-a-table-to-another-schema/:
RENAME TABLE sourceSchema.sourceTable TO targetSchema.targetTable
It's possible that the SQL committee may standardize syntax in this area. So
any Derby syntax would have to be clearly marked in order to not conflict with
future revs of the SQL Standard. Maybe something like the following:
RENAME DERBY objectType sourceSchema.sourceObject TO targetSchema.targetObject
E.g.:
RENAME DERBY FUNCTION mySchema.myFunc TO yourSchema.yourFunc
> Allow "RENAME [...] TABLE TO" an another schema.
> ------------------------------------------------
>
> Key: DERBY-6205
> URL: https://issues.apache.org/jira/browse/DERBY-6205
> Project: Derby
> Issue Type: Improvement
> Reporter: Guillaume Chauvet
>
> It would be very useful to allow tables to be moved to an another schema with
> an extended syntax like this :
> RENAME TABLE table-Name TO {Another schema.}new-Table-Name
> This feature will be a must-have during a database refactoring phase.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
