Copy tables between databases and servers

Moving a table from one namespace to another — or onto a different server entirely — is a routine job that usually turns into an export, a script and a hope. SQL DATA LENS does it from the Server Navigator in three steps.

Select the source and target namespace

In the Server Navigator, select the source namespace first, then the target.

The order matters. SQL DATA LENS takes the selection order as the direction of the copy: the first namespace you select is the source, the second is the target. Selecting them the other way round configures the copy the other way round.

The two namespaces do not have to be on the same server, and they do not have to be the same kind of database — see beyond IRIS for the engines you can copy to and from.

Start the copy

With both namespaces selected, open the main menu and choose:

Tools → Copy Data

The dialogue opens with the source and target databases already filled in from your selection, so there is nothing to configure twice.

Select the source and target tables

On the left, pick the source schema and the source table. On the right, pick the target schema, and then choose one of two options:

• An existing table — data is written into a table that is already there, either overwriting or appending.
• Create new table — SQL DATA LENS creates the table in the target schema. Enter the name for the new table in the input field next to the option.

When everything is configured, click Start.

Worth knowing

• Check the generated target definition before copying into a new table. The Table Viewer shows the DDL for both sides, which is the quickest way to confirm that column types and lengths came across as you expect.
• Copying into an existing table respects that table’s constraints, so a mismatch in a declared length or type will surface as an error on write rather than being silently truncated.
• For a one-off subset rather than a whole table, run the query first and export the result instead — see save result.

The feature is in every current build; the download page has the installer, and the user’s guide covers the rest of the Tools menu.