--注意: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