Exporting Data to another Data Source

Three types of export options exist for exporting data to another data source:

·Connected: users have access to both source and target databases at the time of export. Records are inserted in the target database as they are read from the source. No data is stored locally.

·Disconnected: the source and target databases are not connected at the time of export. This feature uses “DataBags” to store both the data and database schema in a file which can then be imported into another database. The DataBag functionality is superior to the typical export to a text file in that data size is not limited and binary and unprintable characters (such as tabs) correctly interpreted and exported.

·Text file: data is exported from the source database to a text file and visa-versa

Connected

Data can be exported from one table in the source database to a target table or from an SQL query against the source database to a target table.

The section below describes how to use Drag-N-Drop to move contents of table from a source to a target:

1          Establish a connection between two databases. For more information, see “Establishing a Database Connection.”

2          Click the Catalog Details tab.

3          Click the plus sign by Tables in the source database.

4          Click to select the desired table in the source database.

5          While holding the left mouse button, drag the table to the tab connected to the target database. WinSQL will automatically switch to the target database window, allowing you to drop the table in the catalog control. This action is illustrated as follows:

6          When the mouse button is released after dragging the table into the target database, the WinSQL Export Wizard window displays:

The Export to a new table in <database name> option is selected. Note that the name of the connection will vary.

By default, the table name is the same as in the source database; however, it can be modified.

Note: If the table exists in the target database, WinSQL automatically selects the Export to an existing table in <database name>.

7          Click Next. The following window displays:

8          Ensure the displayed create table statement is correct, and then click Next.

Note: WinSQL will convert, as necessary, data types between the two databases. If a particular data type is not supported in the target database, “not supported” displays in this window but can be modified to specify a data type that most closely matches.

The following window displays:

Note:

In the previous illustration, the rows that have lines through them indicate that the columns be ignored. In this example, the user does not wish to export data for the “last_name” and “street” columns.

9          Ensure the data mapping between the two databases is correct, and then click Next. The following window displays:

10          Ensure the export summary information is correct, and then click Finish.

Note: If the export summary information is incorrect, click Modify Query to make the necessary changes.

The data export begins, and status information displays during the process:

When the export has completed, the following window displays:

11          Click OK to continue. The data export is completed.

Disconnected

The disconnected export option uses DataBags to complete the export. DataBags contain a local representation of schema and data from a table in a relational database. Using DataBags, WinSQL can recreate a table along with its data. Therefore, DataBags can be used for either database backup or to move data from one source to another in a disconnected mode.

There are two ways to create DataBags: using table drag-and-drop, or using WinSQL menu options. These methods are described in the following sections.

Once the DataBag is created, perform the steps described in this section to import the contents of the DataBag into the target database.

Disconnected Export: Drag-and-Drop

1          Establish a connection with the source database. For more information, see “Establishing a Database Connection.”

2          Click the Catalog Details tab.

3          Click the plus sign by Tables in the source database.

4          Select the desired table, click the right mouse button, and select Create DataBag:

5          Continue dragging the table to the desired option. If Drop here to create a DataBag to the default folder is selected, the following window displays:

6          Accept the default folder location by clicking Proceed. The following window displays:

7          WinSQL will open the folder where output file is saved.

The data export is completed.

Disconnected Export: Multiple Tables

1          Establish a connection with the source database. For more information, see “Establishing a Database Connection.”

2          From the Tools menu, point to Data To-Go, and then click Create DataBags. The following window displays:

3          Select the tables for which you wish to create the DataBag, and then click Export.

Importing a DataBag into a Target Database

1          Disconnect from the current database.

Note: It is not necessary at this point to connect to the target database; the DataBag Export Wizard will guide you through establishing the connection during the data transfer.

2          From the Tools menu, point to Data To-Go, and then click DataBag Viewer. The following window displays:

3          Select the desired table, and then click Import button. Double-clicking a file name will also invoke the wizard. The following window displays:

4          Click to select the desired table, and then click Next. The following window displays:

5          If you wish to run a SQL script either before or after the data transfer, enter the script in either/or the Pre-Transfer Script or Post-Transfer Script text areas.

6          Click Next to continue.

7          If the information displayed in the Summary window is correct, click Finish to complete the data transfer.

8          A message displays indicating that the data transfer completed successfully. Click OK.

Quick Transfer to a Text File

1          Establish a connection with the source database. For more information, see “Establishing a Database Connection.”

2          Click the Query tab.

3          Select and run the desired query to display the data.

4          Right-click a data row ResultSet window, and then click Export data.  The following window displays:

5          Select the desired export format.

6          Accept the default target file name, or modify it as desired, and then click OK. The data transfer is processed and completed.

Enhanced Transfer to a Text File

The following steps describe how to create an ODBC data source that connects to a text driver. This option provides more flexibility and allows you to run SQL queries on the text file.

1          Establish a connection with the source database. For more information, see “Establishing a Database Connection.”

2          Click the Query tab and type a SELECT query.

3          Click the down arrow by the play icon.

4          Click Export results. The following window displays:

5          Click Next. The following window displays:

6          Click Export to a local text file:

7          Click Next. The following window displays:

8          In the File Location field, enter a file name, including the path, or click Browse to select a destination file.

9          Select or type the desired field separator and the enclosure character.

10          Click Next.

11          On the following window, you may elect to save a template, which can be used at a later time to schedule a task.

12          Click Next, and then click Finish.