Knowledge Base

Document information

Document ID:	1441
Subject:	Transferring, migrating, exporting data from Teradata to Oracle
Creation date:	7/27/10 5:31 PM
Last modified on:	7/27/10 5:31 PM

Transferring data from Teradata to Oracle

Businesses need migrating data from one source to another target from time to time. This is often a major challenge when such migration needs to be done between different types of databases. Moreover, some businesses requires these data transfers periodically based on a schedule.

This article demonstrate how to migrate data between Teradata and Oracle databases, it talks about challenges faced when converting between different data types as well as transferring binary data.

Using WinSQL for data migration

WinSQL is a universal tool that can migrate data from any ODBC compliant data source to any ODBC compliant target.
Since WinSQL is ODBC compliant, you can connect to almost any database currently available in the market today. This includes from heavy duty relational databases like Oracle, MS SQL Server, IBM DB2 and Sybase to single user file bases databases like MS Access, Paradox and DBase.

Data migration challenges

Here are some typical challenges that must be met when transferring data

• Data type conversion - Often databases use different set of data types, which differ in precision and names. It is important to match data types closely as possible to avoid data truncation
• Binary data transfer - Often text files are used to transfer data. This method is fine for alphanumeric data types but falls short if you have binary data types like images, audio, and video
• Logging - It is important to log errors that occur during data transfer. This way users can take appropriate action in case something goes wrong.

WinSQL supports two modes of data migration:

• Connected mode - when you have access to both Teradata and Oracle databases at the same time.
• Disconnected mode when the databases reside on the different network and cannot be connected simultaneously from one client machine

Connected mode

Connected mode is the fastest way to migrate data from one source to a target. In this mode WinSQL can connect to both Teradata and Oracle at the same time, run a SELECT queries against source database and issues INSERT statements against the target database.

Following image displays a screen shot connecting to both databases. The easiest way to initiate data transfer is to select the desired table from Teradata and drop in to Oracle window, or the other way around.



This invokes the Data Transfer wizard in WinSQL. Click here to watch a video tutorial showing how to using WinSQL in this mode to transfer data.

Disconnected mode

Disconnected mode is useful when you cannot connect to both Teradata and Oracle at the same time. This requires two steps:

1. Exporting data from Teradata, saving it to a temporary location
2. Importing back into Oracle from the temporary location.

Historically, users have been using text files to import from the source and then insert data back into target. Typically, these text files contain data in CSV format, Tab delimited, or fixed length.

Although this technique may work in certain cases, it has its own problems:

• Custom code must be written to import/export data
• Cannot export binary data
• Carriage returns, line feeds, tab and other non-printable characters may not get transferred correctly
• Transferring large text value for one field becomes a challenge

WinSQL overcomes these challenges by creating the temporary file in a binary format, which not only handles the above mentioned problems but is generic enough to be used by any database. In WinSQL's speak, this is called a DataBag. DataBags can not only store data, but also the design of a table. This way you can easily recreate the table in the target database. Click here to watch a video tutorial on this subject.

User comments

Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users? Please add it below. Your name and tip will appear at the end of the document text.

Your name:
Your email:	Hide my email address
Verification code:	Enter the verification code you see above more submitting your tip
Tip:	Please limit tips to 1000 characters