Sqlsever--从远程文件导入数据到数据库

一、配置远程地址及文件路径信息

例如：

DECLARE   @VC_FileName varchar(200),  
          @VC_ServerPath varchar(200), --远程服务器路径
          @VC_ServerFile varchar(200), --访问文件
          @VC_LocalPath varchar(200),  --本地建的目录
          @VC_LocalFile varchar(200),  --本地文件地址
          @VC_PassWord   varchar(200), --密码
          @VC_user    varchar(200)    --用户名

--配置服务器地址
SET @VC_ServerPath ='\\111.22.9.2\Transfer\XXXX\XXX业绩周报\XXX'

---配置服务器文件地址
SET @VC_ServerFile = @VC_ServerPath + '\' + @VC_Today + '.xlsx'  

--需注意文件后缀及‘\’问题

---配置本地文件夹地址

SET @VC_LocalPath = 'D:\ETFCHECK\XXX'

---配置本地文件地址

SET @VC_LocalFile  = @VC_LocalPath +  '\' + @VC_Today + '.xlsx'  

--配置能访问服务器的用户名

SET  @VC_user = 'XXX'

--配置能访问服务器的密码

SET  @VC_PassWord = 'XXXXX'

配置本地文件目录时可以如果没有手工建目录则需自动创建，操作如下

--定义一个临时表，获取文件目录信息
DECALRE @T_FileExist TABLE(FileExist VARCHAR(1000))

--现删除临时表信息
DELETE @T_FileExist

--把文件目录信息插入临时表
INSERT @T_FileExist 
EXEC('xp_cmdshell ''dir '+@VC_LocalPath+'\''')  --用cmd命令获取@VC_LocalPath目录下文件信息

IF EXISTS(SELECT * FROM @T_FileExist WHERE FileExist = '系统找不到指定的路径。'OR FileExist = '系统找不到指定的文件。')
BEGIN
   EXEC('xp_cmdshell ''md '+@VC_LocalPath+'''')  --若不存在则自动创建@VC_LocalPath目录
END

--如果存在则删除文件
IF NOT EXISTS(SELECT * FROM @T_FileExist WHERE FileExist = '系统找不到指定的路径。'OR FileExist = '系统找不到指定的文件。')
BEGIN
     EXEC('xp_cmdshell ''DEL'+@VC_LocalFile+'''')
END

二、从远程服务器复制文件到本地

delete @T_FileExist

insert @T_FileExist

EXEC('xp_cmdshell ''dir '+@VC_LocalFile+'''')   --查看本地有没有需要拷贝的文件

IF EXISTS(SELECT * FROM @T_FileExist WHERE FileExist = '系统找不到指定的路径。'OR FileExist = '系统找不到指定的文件。' OR FileExist = '找不到文件')  ---不存在才执行拷贝
  BEGIN
    --复制文件到本地
    EXEC('master.dbo.xp_cmdshell '' bcp "SELECT ''''net use '+@VC_ServerPath+' '+@VC_PassWord+' /user:'+@VC_User+''''' a union all '
      + 'select ''''copy '+@VC_ServerFile+' '+@VC_LocalFile+' '''' union all '
      + 'select ''''net use /delete '+@VC_ServerPath+''''' " queryout "'+@VC_LocalPath+'\copy.bat" -c -T'''
      )  ---使用bcp 命令从文件导出数据到临时表中 最后删除链接
    EXEC('master.dbo.xp_cmdshell '''+@VC_LocalPath+'\copy.bat''')
  END

三、从本地文件中读取数据写入数据库中

---先判断本地目录中是否有文件，有再进行读取和导入操作
DELETE @T_FileExist
  INSERT @T_FileExist 
  EXEC('xp_cmdshell ''dir '+@VC_LocalFile+'''')
  IF NOT EXISTS(SELECT * FROM @T_FileExist WHERE FileExist = '系统找不到指定的路径。'OR FileExist = '系统找不到指定的文件。' OR FileExist = '找不到文件')
  BEGIN
    insert into table 表名
        select 

            字段名1,
            字段名2
     FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel         
        12.0;hdr=yes;database='+@VC_LocalFile+''',[Sheet1$])
    ')
    end