bulk copy[copy into] How do I replace or change the datatype of one column(during bulk copying) in monet db/sybase?

Question

I am trying to migrate my data from sybase to monetdb.(or opposite) for this I am using bcp out from sybase and copy into in monetdb.

The problem is some of the data types are not available in monetdb for example datatime. To solve this problem I can define a function in monetdb which coneverts datetime format to monetdb specific format.

But How to call this function on specific column when I am trying bulkcopy command

COPY INTO TABLE from file using delimiters;

Same situation for bcp in sybase

EDIT 2: (I guess I solved my problem although original question still remains) I wanted to bulk copy data from a csv file in both monetdb and sybase. The csv file had one column with unix timestamps (seconds from 1970). As it is difficult to query using unix time stamp compared to human readable date time (YYYY-MM-DD HH:MM:SS) format, I wanted to convert that timestamp column to date time format).

I could not change the datatype at time of bulk copy but defined(used) functions to translate timestamp in human readable format.

--For Sybase used dateadd function :
select * from gaurav.table where dateadd(ss,t_stamp,'1/1/1970') '2013-02-27 (less than sign) 10:17:29.463114';

--For Monetdb defined a function : 
create function "epoch"(sec INT) returns TIMESTAMP external name timestamp."epoch";
select * from gaurav.table where epoch(t_stamp)  (less than sign) '2013-02-27 10:17:29.463114';

Michael Gardner Michael Gardner · Accepted Answer · 2013-02-27T15:21:26

I'm not sure ab out monetdb, but within Sybase ASE you can define views for your tables that convert the Sybase datetime to the format you need. Once the view is created, you can just BCP directly out of the view, and your data should be ready for your import.

You may be able to BCP into a table view, and accomplish the same thing.

More information on views can be found in the Sybase's Transact-SQL User's Guide

bulk copy[copy into] How do I replace or change the datatype of one column(during bulk copying) in monet db/sybase?

1 Answers