[MonetDB-users] COPY - performing SQL processing while COPYing

Venks venkatesh.mantha at gmail.com
Fri Dec 7 13:48:38 CET 2007


Hi,

Is there are way I can perform some SQL processing while using COPY to
load the data?

For e.g. I need to load a file into a table but would like to TRIM all
the spaces at the beginning and at the end of each column for all
VARCHAR Columns. Below is an example in MySQL where I can load the
data into a variable first and then load the variable into an actual
column.

LOAD DATA ....
(
@col1_temp,
@col2_temp,
@col3_temp
)
SET col1 = TRIM(@col1_temp),
SET col2 = TRIM(@col2_temp),
SET col3 = TRIM(@col3_temp)

@<variable> stores the initial value of each field and then you can
use most of the default SQL functions on the variable before inserting
the data.

The advantage here is the ability to perform some basic
transformations on the data to adhere to the column definitions
instead of loading into a temp table and then performing the
transformations to load into the actual table. This really helps when
loading billions of rows and 100G worth of data.

Thanks,




More information about the users-list mailing list