Bteq Scripts to copy data between two Teradata servers

How do I copy data from multiple tables within one database to another开发者_如何学Go database residing on a different server?

Is this possible through a BTEQ Script in Teradata?

If so, provide a sample.

If not, are there other options to do this other than using a flat-file?

This is not possible using BTEQ since you have mentioned both the databases are residing in different servers.

There are two solutions for this.

• Arcmain - You need to use Arcmain Backup first, which creates files containing data from your tables. Then you need to use Arcmain restore which restores the data from the files

• TPT - Teradata Parallel Transporter. This is a very advanced tool. This does not create any files like Arcmain. It directly moves the data between two teradata servers.(Wikipedia)

If I am understanding your question, you want to move a set of tables from one DB to another.

You can use the following syntax in a BTEQ Script to copy the tables and data:

CREATE TABLE <NewDB>.<NewTable> AS <OldDB>.<OldTable> WITH DATA AND STATS;

Or just the table structures:

CREATE TABLE <NewDB>.<NewTable> AS <OldDB>.<OldTable> WITH NO DATA AND NO STATS;

If you get real savvy you can create a BTEQ script that dynamically builds the above statement in a SELECT statement, exports the results, then in turn runs the newly exported file all within a single BTEQ script.

There are a bunch of other options that you can do with CREATE TABLE <...> AS <...>;. You would be best served reviewing the Teradata Manuals for more details.

There are a few more options which will allow you to copy from one table to another.

Possibly the simplest way would be to write a smallish program which uses one of their communication layers (ODBC, .NET Data Provider, JDBC, cli, etc.) and use that to take a select statement and an insert statement. This would require some work, but it would have less overhead than trying to learn how to write TPT scripts. You would not need any 'DBA' permissions to write your own.

Teradata also sells other applications which hides the complexity of some of the tools. Teradata Data Mover handles provides an abstraction layer between tools like arcmain and tpt. Access to this tool is most likely restricted to DBA types.

If you want to move data from one server to another server then We can do this with the flat file. First we have fetch data from source table to flat file through any utility such as bteq or fastexport. then we can load this data into target table with the help of mload,fastload or bteq scripts.