How to Split a Table into Several Small Tables

Sometimes when migrating from one database to another, we may face the situation when in the source database there is a table with a number of columns than exceeds the maximum number of columns allowed in the target database. Or you may need to split a table into several tables due to the optimization process.

Using Ispirer SQLWays Wizard tool you can do that in two ways:

  • Using the Queries
  • Using the Local Data Type Mapping

To spit a table into several tables using SQLWays Wizard you need to choose “Use Query” on the “Specify Objects” page:

Then you need to specify select queries in the “Select Statement” column and target table names in the “Target Table Name” column. Queries will be used to extract data from appropriate columns from a table and our tool will automatically generate correct definition with the columns that are used in the column list in the select statement. Thus a table name for a new table will be taken from the second column. Please see the screenshot for your reference:

You can specify as many queries as you need, just use “Add Query” option for this purpose.

To split a table into several tables using the local data type mapping, please click on the required table on the “Specify DDL and Data Options” page and uncheck the columns that should be removed from the conversion and run the conversion. Please refer to the screenshot below:

As soon as the first part of the table is migrated, you will need to run the tool once again and specify another set of columns that should be included in the second part of the table:

If you face any difficulties or have any other questions, please contact our support team:

More about Ispirer Systems