Updating an aggregate table from detail table using MLOAD (multiload) or job.

Tools & Utilities
Enthusiast

Updating an aggregate table from detail table using MLOAD (multiload) or job.

Hi All,

I am new to teradata and done some findings on FLOAD, MLOAD and manually creating a job to load data from 1 table to another.

I am able to do FLOAD, mload for loading the data from 1 table to another table.

The thing is

I am not able to update the aggregate table created from the detail table using mload, I am trying from last 4 days but with no success.

The detail table contains 1000000 rows and the aggregate table which contains the aggregated data from detail tables contains 99990 rows.

Now I need to update the aggregated table.

Is it possible using mload? If yes can you guide me the steps to do this?

If not, Is there is any other way to do the same?

Thanks & Regards

saumil sanghvi

Tags (3)
5 REPLIES
Junior Contributor

Re: Updating an aggregate table from detail table using MLOAD (multiload) or job.

Hi Saumil,

simply run a SQL UPDATE, you can do this after the END MLOAD or in a BTEQ script.

Enthusiast

Re: Updating an aggregate table from detail table using MLOAD (multiload) or job.

Hi dnoeth,

Thanks for your reply.

Can you please explain in detail.

I am having a tool generated scrip using tpt which I can update will it work?

Is there any documents which give an idea of manually creating mload script for updating aggregate table from detail table?

Thanks & Regards

saumil sanghvi

Enthusiast

Re: Updating an aggregate table from detail table using MLOAD (multiload) or job.

Hi,

The secenario is

I am having src table as country_Details and target table as CountryMeasure.

Below are the columns of tables

Country_Details   CountryMeasure

PK_Key,           ID column(automatic increment by1),

Country,          Country,

State,            State,

City              Revenue

Revenue

I need to update the data from src table to target using following sql stmt logic (select Country,State,sum(Revenue) from Country_Details group by Country, State).

I am able to insert the data from src to target table when table is empty, but in order to upsert(update+insert) the data from src to target I am facing an issue.

How should I accomplish this in teradata using Mload or any other functions jobs in teradata?

I want to know one thing whether there should be ID key on both sides in order to update the data?

Thanks & Regards

saumil sanghvi

Junior Contributor

Re: Updating an aggregate table from detail table using MLOAD (multiload) or job.

Hi Saumil,

what you need is a simple SQL MERGE.

Based on the tool you actually use, after the END MLOAD or in an APPLY to a DDL Operator.

And of course you need a key to update a row, in your case this seems to be country+state.

Enthusiast

Re: Updating an aggregate table from detail table using MLOAD (multiload) or job.

Hi,

I wrote the below script as follows: I am using teradata 15 client

.Logtable Logtable003;

.Logon tpx/uname,pass;

.Begin Import Mload

 tables tb_c3;  /*target table*/

.Layout Layoutname;

.Field Country varchar(10); /*src column name*/

.Field State varchar(10); /*src column name*/

.Field Revenue number; /*src column name*/

.DML Label DMLlabelname

Do insert for missing update rows;

update tb_c3

set Country=:Country,

      State=:State,

    Revenue=:Revenue

    where State=:State and Country=:Country

    Insert into tb_c3(Country,Revenue) values (:Country,:Revenue);

.End Mload;

/* what should I write here  as you mentioned after End Mload*/

.Logoff;

Thanks & Regards

saumil sanghvi