Using Complete Compare to Mass Import Definitions into ERwin

This lab page was originally used in a CA World presentation. Where there is a link to a "cheat", that link points to a file that has had the previous steps completed. These cheats are provided to help you in case you made a mistake in the previous steps.

Try each step first, so that you understand how to accomplish the task and only use the cheats if you are really stuck.

In this lab, you will be exporting definitions into Microsoft Excel, making modifications, then importing the revised definitions back into your model.

Of course, you could always use the ERwin APIs if you have those skills and tools. However, this approach is provide for those users who need an alternative for using the API.

Preparation

Open ERwin Data Modeler, then the Emovies.er1 model. cheat

In ERwin, generate a table report that has only table definitions cheat

Tools / Report Builder
New
Property Tree: Text

index.1.jpg

Export As: Text
Format: Duplicates
Text Qualifier: "
Delimiter: Comma Delimited

index.2.jpg

index.6.jpg

Save Report as Text file (Use .csv extension). cheat

Open file in Excel cheat

index.7.jpg

Generating DDL Statements

Add a new column to the right of the first line of definitions with the formula:

=CLEAN(CONCATENATE("COMMENT ON TABLE ",+A10," IS """, SUBSTITUTE(+B10,CHAR(10), " "),""";"))

Your syntax may vary based on DBMS you are using.

Copy this down the column. This formula "generates" SQL. cheat

Make changes to the definitions only. Notice how the SQL statements change as well. You might even want to spell check the definitions while you are at it.

index.8.jpg

Review definitions for quotes, special characters, etc.

Copy the entire Column and paste it into Notepad. Again review for any special characters.

Save the file with a .sql extension. cheat

Complete Compare

Go back to ERwin model and perform a Complete Compare against text file.

index.9.jpg

index.10.jpg

Import Changes as desired via the Complete Compare - see the yellow arrows point to the data model in the left hand frame.

index.11.jpg

You're Done

Open tables to review changes.

The use of Excel above does a few things:

Pulls together the SQL components
CONCATENATE()
Removes line breaks (substitutes a space for a line break)
SUBSTITUTE(+B10,CHAR(10), " ")
Removes all non-printable characters
CLEAN()