How to transfer data between databases using SELECT statement in Teradata
So, I am stuck with this Teradata issue and I am reaching out to the community for advice as I am new to the TD platform. I am currently working with a Teradata datastore and have an interesting challenge to solve. We are currently storing our information in a live production database, but want to host tables in another database before using FastExport to export files. Basically we want to move our tables to the database to take a quick snapshot.
I have looked into various solutions and am not sure how to do this. I need to be able to automate the process of creating a table from one DB in Teradata to another. The tricky part is that I would like to create many tables from the original table using the WHERE clause. For example, I have a transaction table and want to take a snapshot of the transaction table for a specific date range per month. This means that the original Transaction table will be split into many tables like Transaction_May2001, Transaction_June2001, Transaction_July2001, etc. Etc.
thank
source to share
This assumes two databases that you reference the same physical Teradata installation.
You can use a construct CREATE TABLE AS
to accomplish this:
CREATE TABLE {MyDB}.Transaction_May2001
AS (
SELECT *
FROM Transaction
WHERE Transaction_Date BETWEEN DATE '2001-05-01' AND '2001-05-31'
)
{UNIQUE} PRIMARY INDEX ({Same PI definition as Transaction Table})
WITH DATA AND STATS;
If you do not specify an explicit PI in CREATE TABLE AS, Teradata will take the first column of the SELECT clause and use it as the PI of the new table.
Otherwise, you would like to use the Teradata utility as suggested by ryanbwork in a comment to your question.
source to share