|
From: Robert M. <rob...@gm...> - 2006-08-25 10:26:10
|
On 8/24/06, Steven Rose <ucf...@gm...> wrote: > Is there any way to create table scripts and data scripts for a specific > Database. For example if i am in a Oracle Database and want to generate a > Script to create a table in my Oracle database in my mysql Database. I > know there is a way to graphically do this using DB Copy Plugin but I > need to do this using a script. Is this possible or is this a feature > request. Thanks. > Hi Steve, DB Copy plugin can be configured to write a script file containing a rough approximation of the copy operation that was performed. The script gets placed in <user.home>/.squirrel-sql/plugins/dbcopy/scripts and is called <source>_to_<destination>.sql. I say it's a rough approximation because insert records with binary columns (BLOB, VARBINARY, LONGVARBINARY, etc.) from scripts isn't well supported by most databases. So it currently creates the "create" statements, but doesn't include the insert statements for tables with binary columns. This plugin gets help from hibernate to translate the columns correctly from one database dialect to another. However, there is another way to accomplish what you are asking which is quicker and might work ok for Oracle -> MySQL. You can open a session to Oracle, right click on the table and choose "create table script". That puts the table create statement in the SQL tab. Next, right-click on the table and choose "create data script". This will place an insert statement into the SQL tab for every record in that table. This is limited to one table at a time, so it may be tedious if you have a large number of tables to copy. The biggest drawback to this approach is that it produces SQL create statements that may only work on the database type that it was created from. For instance, if you have a NUMBER column in Oracle, the script will have NUMBER column types which aren't valid SQL types in MySQL. This is one of the reasons that I wrote the DBCopy plugin. Rob |
