在存储过程中用ole对象(Scripting.FileSystemObject)读写文件

--注意:1.表的字段都改为字符型！！;2.默认的表名为gic_comp和gic_secur；3.如果出现截断的情况，
--应该是值的长度和表中定义的长度不一样。更改表的字段的定义,长度改长就可以.
--更改系统配置，允许使用OLE对象
sp_configure 'show advanced',1
go
reconfigure
go
sp_configure 'ole automation procedures',1
go
reconfigure
go

create proc readfile
@strPath nvarchar(512)
as
DECLARE   @object   int  
  DECLARE   @hr   int  
  DECLARE   @src   varchar(255),   @desc   varchar(255)  
  Declare   @tmp   int  
  declare   @msg   varchar(3000)  
  declare @firstchar char
  declare @tablename varchar(100)
  declare @sql nvarchar(4000)
  declare @key varchar(100)--gvkey
  declare @fields varchar(600)--保存表的所以字段
  declare @f varchar(100)--保存表的单个字段
  declare @v varchar(200)--保存字段对应的值
 
  --创建Scripting组件实例  
  EXEC   @hr   =   sp_OACreate   'Scripting.FileSystemObject',   @object   OUT  
  IF   @hr   <>   0  
  BEGIN  
        EXEC   sp_OAGetErrorInfo   @object,   @src   OUT,   @desc   OUT 
          RETURN  
  END  
   
 
  --打开文件,创建textstream对象，其句柄@tmp
  EXEC   @hr   =   sp_OAMethod   @object,   'OpenTextFile',   @tmp   OUTPUT   ,@strPath  
  IF   @hr   <>   0  
  BEGIN  
        EXEC   sp_OAGetErrorInfo   @object  
          RETURN  
  END  
   

  set @firstchar=''
  SET   @msg=''  
  set  @sql=''
  --读文件，读一行
  EXEC   @hr   =   sp_OAMethod   @tmp,   'Readline',   @msg   OUT 
  set @msg=ltrim(rtrim(@msg))
  --处理该行
  --1.取行第一个字符
  set @firstchar=left(@msg,1)
  --2.根据@firstchar 的不同进行不同的处理,(H:表,I:插入,C:更新,R:删除),其它忽略
  if 'H'=@firstchar
    begin
    set @tablename=substring(@msg,21,charindex('|',@msg,21))--取表名
    set @tablename=left(@tablename,patindex('%[0-9]%',@tablename)-1)
    end  
  else if 'I'=@firstchar--插入
    begin
    set @msg=replace(@msg,'|',''',''')
    set @sql='insert into ' +@tablename+'  select '''+@msg+''''
    exec(@sql)
    end
  else if 'C'=@firstchar--更新
   begin
   set @key=''
   select @fields=isnull(@fields+','+name,name) from syscolumns where id=object_id(@tablename)
   set @fields=right(@fields,len(@fields)-11)+','--len(type,gvkey,)=11,去掉左边两个字段
   set @key=substring(@msg,3,charindex('|',@msg,3)-3)
   set @msg=right(@msg,len(@msg)-charindex('|',@msg,3))+'|'--去掉左边两个值
   set @sql='update '+@tablename+' set '
   while charindex(',',@fields,1)<>0
       begin
         set @f=left(@fields,charindex(',',@fields,1)-1)--取一个字段
         set @fields=right(@fields,len(@fields)-len(@f)-1)--去掉最左边一个字段
        set @v=left(@msg,charindex('|',@msg,1)-1)--取最左边一个值
        set @msg=right(@msg,len(@msg)-len(@v)-1)--去掉最左边一个值
         --组合sql语句
         set @sql=@sql+ @f+'=case @v  when '''' then '+@f+' else @v end'
         set @sql=@sql+'  where gvkey='+@key
      exec sp_executesql @sql,N'@f as varchar(100),@v as varchar(100),@key as varchar(100)',@f=@f,@v=@v,@key=@key
   set @sql='update '+@tablename+' set '
       end 
   end

  else if 'R'=@firstchar--删除
    begin
    set @key=substring(@msg,3,charindex('|',@msg,3)-3)
    set @sql='delete from '+@tablename+' where gvkey='+@key
    exec(@sql)
    end

  
while @hr=0
begin
  set @firstchar=''
  SET   @msg=''  
  set  @sql=''
  --读文件，读一行
  EXEC   @hr   =   sp_OAMethod   @tmp,   'Readline',   @msg   OUT 
  set @msg=ltrim(rtrim(@msg))
  --处理该行
  --1.取行第一个字符
  set @firstchar=left(@msg,1)
  --2.根据@firstchar 的不同进行不同的处理,(H:表,I:插入,C:更新,R:删除),其它忽略
  if 'H'=@firstchar
    begin
    set @tablename=substring(@msg,21,charindex('|',@msg,21))--取表名
    set @tablename=left(@tablename,patindex('%[0-9]%',@tablename)-1)
    end  
  else if 'I'=@firstchar--插入
    begin
    set @msg=replace(@msg,'|',''',''')
    set @sql='insert into ' +@tablename+'  select '''+@msg+''''
    exec(@sql)
    end
  else if 'C'=@firstchar--更新
   begin
   set @fields=null
   set @key=''
  
   select @fields=isnull(@fields+','+name,name) from syscolumns where id=object_id(@tablename)
   set @fields=right(@fields,len(@fields)-11)+','--len(type,gvkey,)=11,去掉左边两个字段
   set @key=substring(@msg,3,charindex('|',@msg,3)-3)
   set @msg=right(@msg,len(@msg)-charindex('|',@msg,3))+'|'--去掉左边两个值
   set @sql='update '+@tablename+' set '
   while charindex(',',@fields,1)<>0
       begin
         set @f=left(@fields,charindex(',',@fields,1)-1)--取一个字段
         set @fields=right(@fields,len(@fields)-len(@f)-1)--去掉最左边一个字段

        set @v=left(@msg,charindex('|',@msg,1)-1)--取最左边一个值
        set @msg=right(@msg,len(@msg)-len(@v)-1)--去掉最左边一个值
         --组合sql语句,一个字段一个字段的改
          set @sql=@sql+ @f+'=case @v  when '''' then '+@f+' else @v end'
          set @sql=@sql+'  where gvkey='+@key
          exec sp_executesql @sql,N'@f as varchar(100),@v as varchar(100),@key as varchar(100)',@f=@f,@v=@v,@key=@key
          set @sql='update '+@tablename+' set '
       end 
    end
  else if 'R'=@firstchar--删除
    begin
    set @key=substring(@msg,3,charindex('|',@msg,3)-3)
    set @sql='delete from '+@tablename+' where gvkey='+@key
    exec(@sql)
    end

end

--用法示例
--readfile 'd:/ff.txt'

----表的定义
--USE [test]
--GO
--/****** 对象:  Table [dbo].[GIC_Comp]    脚本日期: 03/04/2010 09:36:39 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--SET ANSI_PADDING ON
--GO
--CREATE TABLE [dbo].[GIC_Comp](
-- [Type] [char](1) NULL,
-- [GVKEY] [varchar](6) NULL,
-- [CONM] [varchar](255) NULL,
-- [COSTAT] [char](1) NULL,
-- [FIC] [varchar](3) NULL,
-- [GGROUP] [varchar](4) NULL,
-- [GIND] [varchar](6) NULL,
-- [GSECTOR] [varchar](2) NULL,
-- [GSUBIND] [varchar](8) NULL,
-- [LOC] [varchar](3) NULL
--) ON [PRIMARY]
--
--GO
--SET ANSI_PADDING OFF
-------------------------------------------

--USE [test]
--GO
--/****** 对象:  Table [dbo].[GIC_Secur]    脚本日期: 03/04/2010 09:37:09 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--SET ANSI_PADDING ON
--GO
--CREATE TABLE [dbo].[GIC_Secur](
-- [Type] [char](1) NULL,
-- [GVKEY] [varchar](6) NULL,
-- [IID] [varchar](3) NULL,
-- [DSCI] [varchar](28) NULL,
-- [EXCHG] [varchar](100) NULL,
-- [ISIN] [varchar](12) NULL,
-- [SECSTAT] [char](1) NULL,
-- [SEDOL] [varchar](7) NULL,
-- [TIC] [varchar](20) NULL,
-- [TPCI] [varchar](8) NULL
--) ON [PRIMARY]
--
--GO
--SET ANSI_PADDING OFF