BulkLoad function

The BulkLoad function loads data from a client file into an existing time series.

Syntax

BulkLoad (ts       TimeSeries, 
           filename lvarchar,
           flags    integer default 0) 
returns TimeSeries;
ts
The time series in which to load data.
filename
The path and file name of the file to load.
flags
Valid values for the flags argument are described in The flags argument values. The default is 0.

Description

The file is located on the client and can be an absolute or relative path name.

Two data formats are supported for the file loaded by BulkLoad:
  • Using type constructors
  • Using tabs

Each line of the client file must have all the data for one element.

The type constructor format follows the row type convention: comma-separated columns surrounded by parentheses and preceded by the ROW type constructor. The first two lines of a typical file look like this:
row(2011-01-03 00:00:00.00000, 1.1, 2.2)
row(2011-01-04 00:00:00.00000, 10.1, 20.2)
If you include collections in a column within the row data type, use a type constructor (SET, MULTISET, or LIST) and curly braces surrounding the collection values. A row including a set of rows has this format:
row(timestamp, set{row(value, value), row(value, value)}, value)
The tab format is to separate the values by tabs. It is only recommended for single-level rows that do not contain collections or row data types. The first two lines of a typical file in this format look like this:
2011-01-03 00:00:00.00000   1.1     2.2
2011-01-04 00:00:00.00000   10.1    20.2

The spaces between entries represent a tab.

In both formats, the word NULL indicates a null entry.

When BulkLoad encounters data with duplicate time stamps in a regular time series, the old values are replaced by the new values. In an irregular time series, when BulkLoad encounters data with duplicate time stamps, the following algorithm is used to determine where to place the data belonging to the duplicate time stamp:
  1. Round the time stamp up to the next second.
  2. Search backwards for the first element less than the new time stamp.
  3. Insert the new data at this time stamp plus 10 microseconds.

This is the same algorithm as used by the PutElem function, described in PutElem function.

Returns

A time series containing the new data.

Example

The following example adds data from the sam.dat file to the stock_data time series:
update daily_stocks 
set stock_data = BulkLoad(stock_data, 'sam.dat')
   where stock_name = 'HCLTECH';