How to split a Data Export by row value?

Tools & Utilities

How to split a Data Export by row value?

Hi all,

I'm not very experienced with the Load and Unload Tools, I just got a simple BTEQ Script to export a single resultset to work. Is there a way to split the export by a row value, how can I do that? What I want is a separate flat export file for each value in my result set, e.g. 10 files for 10 customer id's (customer_1.csv, customer_2.csv...) and don't want to do that manually. Please give me a kick in the right direction :-)

4 REPLIES
Enthusiast

Re: How to split a Data Export by row value?

Hi,

You can split your export data by using a variable in WHERE condition. e.g. you can put this select statement in a macro and then calll the macro, and pass the value as paramter for where clause.

Khurram

Re: How to split a Data Export by row value?

To reduce database load and save time my intention is to execute the statement only once and split the resultset export dynamically based on a defined row value after the resultset is received. Is it possible to do something like this on teradata level, or would I have to that afterwards with an external script that splits the export? In my understanding in the example with 10 customer id's I would have to call and execute a parameterized macro ten times manually, or did I miss something?

It is no one time operation, the query will be executed regularly with different and much more then the ten customers in my example.

Enthusiast

Re: How to split a Data Export by row value?

I think then it would be good for you to use stored procedure, write a stored procedure to loop through a range you want to process, pass parameters of date or CustomerID. 

Stored procedure give you the best control over repeating statements.

Khurram

Re: How to split a Data Export by row value?

I still don't see how to avoid multiple query execution. Do I have to use a cursor and loop through the resultset, is it even possible to export data to a file within a stored procedure? I did some research and can't find examples how to do that. Or would I have to insert the filtered resultsets in a multiple volatile tables that will be exported after sp execution within a bteq script?