Segmented Data Exports to Text Files of Specified Format in esProc

Problem source: http://bbs.csdn.net/topics/370240857

Below is a selection from a database table, TB1:

esProc_sql_segment_output_1

Based on this data, you need to generate text files of the specified format and with the following names (yyyyMMddHH) by the hour.

2011101106.txt

1|2011-10-11 6:24:21|aaa

2|2011-10-11 6:30:45|dddd

2011101107.txt

3|2011/10/11 7:10:12|dsf

4|2011/10/11 7:50:38|dffew

5|2011/10/11 7:59:59|dfae

2011101108.txt

6|2011/10/11 8:00:00|edfae

After grouping data by the hour, you export every group to a text file according to the specified format (|) and name it after the hour:

esProc_sql_segment_output_2

A1: Use a SQL to create a database cursor sorted by CreateTime. db represents the connection to a database.

A2-B2: Group data by CreateTime (yyyyMMddHH), and fetch data by loop, one group each time, to export, through B2, to text files named after the create time. And specifiy format for the text data at the same time.

Text files are as follows:

esProc_sql_segment_output_3

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in SQL-related Puzzle and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s