You can use Power*Architect to quickly copy data from one database platform (such as Oracle) and create a

verbatim copy on another database platform (such as PostgreSQL). Power*Architect automatically checks

for foreign key constraints in the target database and orders the inserts and deletes accordingly.

You can also use Power*Architect to copy data if the source and target databases are on the same database

platform. However, in this case, it's usually faster and more reliable to use the database vendor's own tools

to do a "dump-and-restore".

If you want to do something more complex than a verbatim copy, use an ETL tool such as Kettle. ETL

tools offer great flexibility in extracting, transforming, and loading data between databases. For more

information, see the section called “Using Kettle Jobs” .

To copy data:

1. Select Tools » Copy Table Data. The Copy table data dialog box appears.

2. Select the Source and Destination databases. If necessary, click Database Connection Manager to set

up a new database connection.

3. Select the Truncate Destination Table check box to delete all existing data in the destination tables

before copying the data from the source tables.

Copying and Transforming Data

52

Warning: Only use this option if you are sure you want to delete the existing data in the destination

tables.

4. Click OK.

You can use Power*Architect to create a Kettle job, which you can then use to create multiple

transformations based on a data model you've created in Power*Architect. You would typically create a

Kettle job to copy data to a new database you've created through reverse engineering.

Note: The Kettle ETL tool is provided by Pentaho as free and open source software. SQL Power does not

maintain or distribute Kettle. To obtain a copy, visit kettle.pentaho.org [http://kettle.pentaho.org/] .

Before you create a Kettle job, you must use reverse and forward engineering to create a new data model

and database.

Before creating a Kettle job, ensure you've completed the prerequisites (see the section called “Before

Creating a Kettle Job” ).

Note: You can view or change the location of the Kettle (ETL) log file in user preferences. For more

information, see the section called “Setting User Preferences” .

1. Open the project containing the data model you want to use for the Kettle job.

2. Select ETL » Create Kettle Job. The Create a Kettle Job dialog box appears.

Copying and Transforming Data

53

3. Enter the following information:

Job Name Enter a name for the job.

Target Database Select the database connection for the target

database.

Click Properties to view the connection and

modify it if necessary. Ensure the connection

contains the following information:

• General tab - Enter all the required connection

properties for the database platform. (See

the section called “Setting up Database

Connections” .)

• Kettle tab - Enter the hostname, port, and

database for the target database, if applicable. If

a field does not apply to the database platform,

it will be disabled. You do not have to enter a

login name and password.

Note: The hostname, port, and database

information may be entered automatically based

on the information on the General tab.

Schema Name Enter the name of the schema in the target database

that contains the target tables. If the target database

doesn't contain any schemas, or the target tables

are in the default schema, you can leave this field

blank.

Default Join Type Select the join type to use in all merge-joins.

Merge-joins are used to create tables with multiple

sources.

Copying and Transforming Data

54

Note: Merge-joins that are created in

transformations from Power*Architect will

usually have to be updated manually, since

Power*Architect cannot tell which fields to

compare during the join.

Save Job to File Select this option to save the Kettle job settings and

transformations to a file. Click Browse and select

the location and filename.

Save Job to Repository Select this option to save the Kettle job settings and

transformations in a repository.

In the Repository list, select the database

connection for the repository. You can use a

connection you have set up previously (if the

database contains a repository) or you can set up

a new connection to a repository. (See the section

called “Setting up Database Connections” .)

Click Properties to view the connection and

modify it if necessary. Ensure the connection

contains the following information:

• General tab - Enter all the required connection

properties for the database platform. (See

the section called “Setting up Database

Connections” .)

• Kettle tab - Enter the hostname, port, and

database for the repository, if applicable. If a

field does not apply to the database platform, it

will be disabled. Enter the repository login name

and password.

Note: The hostname, port, and database

information may be entered automatically based

on the information on the General tab.

4. Click OK to create the Kettle job and transformation files.

If you are using a repository, you are prompted to select the directory location in the repository where

the files will be saved.

Once the job has been created, a window appears with the steps you need to complete before running the

Kettle job.

Note: The transformation files are stored in the same location as the Kettle job. You must use Kettle to

run the job.