Synchronizing databases with generated SQL

Based on the output of the comparison of the databases, the SQL DataTool can generate the SQL code for you. There are a few options available within SQL DataTool.

Create script from single database

When you generate an overview of only one database (no target database is selected), the SQL code that is generated is based on the creation of the database. Each object type will result in the SQL statement to create that specific object. This means that when you click on the row containing the Schema, the SQL code for the complete schema and underlying objects is generated.

If you click on another object type, e.g. a table or a view, the SQL code for the creation of that object is generated. This SQL code will include all underlying objects, like columns, indices and keys.

When you click on an index or key, the creation SQL script for that index or key will be generated.

Create script from comparing databases

When you select both the source and target databases, you will be able to review the differences between the databases. When you use this option, SQL DataTool can generate scripts for you that creates, alters or drops objects from the target or source database. Depending on the cell row and column that you click on, SQL DataTool will generate a script based on the object (row selection) and database (column selection).

When you use this compare option, only the objects that actually differs between the databases will be converted into SQL script. This means for example that no SQL script is generated for tables that contain the same columns with the same datatype and length etc.

The result of the comparison is displayed in the same result table. This table now contains multiple columns: both for the source and target database. For easy comparison, the text within the results has colors:

• Green: no difference between the objects and the underlying objects
• Orange: a difference exist in the underlying objects (e.g. the table is orange due to a missing column or index)
• Red: The object itself is different or missing in one of the two databases

Besides this, it is possible to filter on objects (e.g. tables or views) and there is a view that only highlights differences. Both options are available at the top of the result.

Generate SQL script

To generate the SQL script from within the compare option, simply double click on the line of which you want to have the script created. The script that is generated, is depending on the line and column that you have clicked on.

Selected column

When you click on one of the first three columns (the source database columns), the script generates the SQL that enables you to update the target database based on the source database. This means that if for example a table is missing in the target database, the script will add a create table statement for this table based on the table in the source database.

If you click on the latter two columns, the opposite SQL statement will be generated. So as per the example above, where the source database contains a table that is not available in the target database, a drop table statement for this table will be generated. The goal is to align the source database based on the target database contents.

Selected line

Within SQL DataTool it is possible to sync a complete database, but you can also generate scripts per object or even sub object (for example an index on a table or a column within a table). The script generates the SQL statement based on the line that you click on.

When you click on the Schema object line, a script is generated that contains all differences between the databases. The script will exist of the underlying objects within the database. As mentioned in the column-section, the generated script is based on the source or target database.

If you click on the table, view or procedure object lines, the script is generated which has all the underlying objects of that specific object. So when you click on a table, the columns, indexes etc are part of the SQL statement.

And finally, when you double click on a column (or index), a statement is generated based on the table, but only the differences of that column (or index) are generated into the script.

This detailed level of script generation allows you to really specify what you want to synchronize between the databases.