Sybase IQ load table 语句的自动生成的存储过程

CREATE PROCEDURE "DBA"."dynamic_load"(@tablename varchar(100),
     @tableown varchar(100),
     @columnDelimiter varchar(8),
     @rowDelimiter varchar(8),
     @datapath varchar(100),
     @msglogpath varchar(100),
     @rowlogpath varchar(100))
as
begin
  declare @tablecolumn varchar(30)
  declare @sqltext varchar(3000)
  declare @i int
 
  set @sqltext ='LOAD TABLE '+@tableown+'.'+@tablename+'('
  declare dynamic_load_cur cursor for select column_name from sp_iqcolumn(@tablename,@tableown)
  open dynamic_load_cur
  fetch dynamic_load_cur into @tablecolumn
  set @i=0
  while @@sqlstatus=0
  begin
    --处理当前记录
    set @i=@i+1
    if @i=1
       set @sqltext=@sqltext+@tablecolumn
    else
       set @sqltext=@sqltext+' '+@columnDelimiter+','+@tablecolumn
    fetch dynamic_load_cur into @tablecolumn
  end
  set @sqltext=@sqltext+' ' + @rowDelimiter+')'
  set @sqltext=@sqltext+' FROM ' + @datapath + ' ESCAPES OFF' + ' QUOTES ON' + ' NOTIFY 1000000'
  set @sqltext=@sqltext+ ' IGNORE CONSTRAINT UNIQUE 0,DATA VALUE 0'
  set @sqltext=@sqltext+' message log ' + @msglogpath + ' row log ' + @rowlogpath
  set @sqltext=@sqltext+' only log unique,data value '+ ' log delimited by ' + @columnDelimiter
  set @sqltext=@sqltext+ ' WITH CHECKPOINT ON'
  message @sqltext type info to client
  close dynamic_load_cur
  deallocate cursor dynamic_load_cur
  set temporary option Conversion_error='Off'
  execute(@sqltext)
  commit
end

 

 

 

 

 

---调用的语句

begin
  declare @tablename varchar(100)
  declare @tableown varchar(100)
  declare @columnDelimiter varchar(8)
  declare @rowDelimiter varchar(8)
  declare @datapath varchar(100)
  declare @msglogpath varchar(100)
  declare @rowlogpath varchar(100)
 
  set @tablename='FIX_MODULE_INFO'
  set @tableown='BIALL_SFM'
  set @columnDelimiter=''','''
  set @rowDelimiter='''//x0a'''
  set @datapath='''/usr/iq127/upload/biall/*.csv'''
  set @msglogpath='''/usr/iq127/upload/test_msg.log'''
  set @rowlogpath='''/usr/iq127/upload/test_row.log'''
  exec dynamic_load @tablename,@tableown,@columnDelimiter,@rowDelimiter,@datapath,@msglogpath,@rowlogpath
end; 

 

注意：如果不能导入，请在输出的load 语句中，调整最后一列为主键。

说明：上面的存储过程转载了别人的成功，具体的url忘记。