Usage of load table in Sybase

Usage of load table in Sybase

With Sybase IQ as the storage of data warehouse, it does have its unique characteristics and advantages, but in the data ETL, if the use of INSERT, update mode, the speed is too slow, about 2 seconds or so a piece of data. Fortunately, IQ provides a load statement to solve this problem.

format of the LOAD statement:[Code]LOAD TABLE [owner].Table-name[(load-specification, ...)] from 'filename-string', ...[FORMAT {' ASCII ' | ' Binary '}]... [delimited by string]... [STRIP {on | OFF}]... [QUOTES {on | OFF}]... [ESCAPES {on | OFF}][ESCAPE CHARACTER CHARACTER] [With CHECKPOINT on| OFF]... [load-options][/code]

Here is the statement from load to table f_inn_ia_daily_sum from a text file:

Set Temporary optionDate_order=YMD;Load TableF_inn_ia_daily_sum (Org_sid'+|+', Deal_sid'+|+', All_time_sid'+|+', R_count_dim_sid'+|+', T_tax_stor_cost'+|+', T_stor_cost'+|+', T_stor_sum'+|+', Created_dt'x0a') from '/load_data/f_inn_ia_daily_sum.txt'ESCAPESOFFQUOTESOFFNOTIFY100000 with CHECKPOINT  on;COMMIT

Where +|+ is the delimiter for the field, x0a is the delimiter of the record, which is the carriage return (in a text file).

Example one:

1, the content format of the text file is:

abc|defgh|aaaa|bbbbbb|ccccc|ddddd|eeee| FFFFABC|defgh|aaaa|bbbbbb|ccccc|ddddd|eeee| FffF ABC|DEFGH|AAAA|BBBBBB|CCCCC|DDDDD|EEEE|FFFF

2,load Table statement:

Load Tablemy_table (Org_sid'|', Deal_sid'|', All_time_sid'|', R_count_dim_sid'|, T_tax_stor_cost'|', T_stor_cost'|', T_stor_sum'|', Created_dt'\x0a'-line break (16 binary) for each line in the data file from'/Load_data/LoadTest.txt'---The path of the file to be loaded escapes offquotes offnotify 100000WITH CHECKPOINT on; COMMIT;

File with ' | ' As delimiters, newline characters for each line are wrapped with ' \x0a ', and the usual line breaks are ' \x0d\x0a ' (carriage return, line feed) or ' \x0a ' (line feed);

Load table syntax writes to the stored procedure and then directly invokes the stored procedure file to load the library;

Example two:

1, the text format is:

20171222|&@001731edae78|& @absent |& @absent |&@001731edae78|&@|20171222|&@001bb958bc07| & @absent |& @absent |&@001bb958bc07|&@|20171222|&@001bfc067636|& @absent |& @absent | &@001bfc067636|&@|

2,load Table statement:

Set Temporary optionConversion_error= 'OFF'; Load TableT_station_match_result (Load_dt'|&@' NULL('//xd0'), Op_station'|&@' NULL('//xd0'), IP'|&@' NULL('//xd0'), Mac'|&@' NULL('//xd0'), Mobile'|&@|'  NULL("') ) from '"+ file_path+"'ESCAPESOFFQUOTESOFFNOTIFY100000  with CHECKPOINT  on;

File_path:txt file path (absolute path)

Usage of load table in Sybase