Toad Data Studio 1.0 - User Guide

Automate Creating and Refreshing Snapshots in Local Storage

Summary

In this tutorial you will learn how to create a snapshot in Local Storage and how to use Automation to create and refresh snapshots.

Note: This feature is available in the Toad Data Studio Professional Edition only.

In this tutorial you will learn:

• How to create snapshots in Local Storage
• How to create snapshots using an Automation script
• How to refresh snapshots using an Automation script

You will need:

• The Toad Sample Database (or a similar sample database)

Watch Automate Creating and Refreshing Snapshots to see a video presentation of this tutorial.

Introduction

Local Storage snapshots are a very useful tool. You can use them to temporarily store data for reporting or querying purposes, similar to Local Storage tables. Snapshots however have an additional feature—they can be refreshed at any time to reflect the current data in the source database. This gives you the added flexibility to import and manipulate data, and then easily refresh it from the source data.

You can save data as a Local Storage snapshot in a variety of different ways in Toad. In addition, you can also create and refresh snapshots using an Automation script.

Create Snapshots in Local Storage

There are several different ways to create a snapshot in Local Storage.

Create a Snapshot from a Table

1. To save a table as a Local Storage snapshot, in the Object Explorer, right-click a table and select Local Storage.

2. In the Save to Local Storage dialog, select Snapshot. Then select or create a database, and select or create a snapshot name. If this is the first time you have used your Local Storage, you must first create a new database. Simply enter a name in the Database field.

   Note: You can also designate a primary key and modify the data types using this dialog.

3. After the data is exported, click the hyperlink in the Success message that displays to open and review your new snapshot.

Refresh a Snapshot

• To refresh the snapshot you just created, in the Object Explorer, right-click the snapshot and select Operations | Refresh Snapshot.

Create a Snapshot from a Result Set

1. Connect to the Toad Sample Database.
2. Select Tools | Query Builder | Query Builder.
3. Build any query you like using the Toad Sample Database. Click Execute SQL to execute the query.
4. In the Results tab, right-click within the result set's data grid and select Send To | Local Storage.

5. In the Save to Local Storage dialog, select Snapshot. Then select or create a database, and select or create a snapshot name.

   Note: You can also designate a primary key and modify the data types using this dialog.

6. After the data is exported, click the hyperlink in the Success message that displays to open and review your new snapshot.

Create a Snapshot from the Import Wizard

You can also save data to Local Storage when you import data using the Import Wizard.

1. To demonstrate this feature, connect to the Toad Sample Database.
2. Select Tools | Import Export Data | Import Wizard.
3. Click Next on the Welcome page.
4. Click Add Query on the Import File page. Click Next. On the Enter Query page, enter Select * from ADDRESS. Click Next.
5. On the Select Target page, select Snapshot. Then select a database and enter a name for your new snapshot.
6. Complete the remaining wizard pages and click Finish to import the data into the snapshot in Local Storage.

Create a Snapshot Using Automation

You can automate the process of saving data to a Local Storage snapshot. These Automation activities allow you to save their output as a Local Storage snapshot: Execute Script and Clean Data (in addition to the Import Wizard).

1. To demonstrate how to create a snapshot using Automation, connect to the Toad Sample Database.
2. Select Tools | Automation to open an Automation window.
3. Click the Execute Script activity in the Toolbox to add it to the Automation design window.

4. In the Activity Input tab, enter the following query: Select * from ADDRESS. Then select Local Storage and Snapshot in export options section.

   

5. Now click the Clean Data activity in the Toolbox to add it to the design window.

6. In the Activity Input tab, in the Export to Local Storage section, select a database and select Snapshot. This will instruct your script to export the modified data to a snapshot in Local Storage.

   

7. The purpose of this example was to demonstrate which Automation activities allow you to save a snapshot. You can close the Automation window now, with or without saving the script.

Refresh a Snapshot Using Automation

After you create a snapshot in Local Storage, you can automate the process of refreshing the snapshot.

1. Select Tools | Automation to open a new Automation window.
2. Click the Refresh a Snapshot activity in the Toolbox to add it to the Automation design window.
3. In the Activity Input tab, select Local Storage.
4. In the Database field, select the Local Storage database containing the snapshot you want to refresh.
5. In the Snapshot field, select the snapshot you want to refresh.

Schedule A Script

See Schedule Your Script to learn how to schedule the Automation script.

Related Topics

Getting Started with Automation