通过sql server存储过程自动下载网络csv文件，来更新数据库

本文链接：https://blog.youkuaiyun.com/liguiping2000/article/details/6877811

目的：呼叫中心系统（sql server）的客户资料要与erp系统(java web+oracle+tomcate)里的客户资料同步。

做法：1，erp系统定时把数据导出放入一个csv文件

2，sql server通过存储过程定时通过外网（http://www.xxx.com/upload/xxxx.csv）下载那个csv文件，来更新本地数据库（记录存在就更新，不存在就插入）。

下载把sql server这边的代码贴上：

CREATE PROCEDURE proc_insert_customer
AS
DECLARE  @vbs_path VARCHAR(255), @http_path VARCHAR(255), @download_file_path VARCHAR(255), @cmd_download VARCHAR(500), @tempcol VARCHAR(500)

SET @vbs_path = 'C:\Program Files\Microsoft SQL Server\80\Tools\filedownload.vbs'
SET @http_path = 'http://www.xxxx.com.cn/upload/customer/wang_dian_customer.csv'
SET @download_file_path = 'C:\Program Files\Microsoft SQL Server\80\Tools\wang_dian_customer.csv'
SET @cmd_download =  replace(@vbs_path,' ','" "') + ' ' + @http_path + ' ' + replace(@download_file_path,' ','" "') 
EXEC master.dbo.xp_cmdshell @cmd_download

IF object_id('tempdb..#tempCustomer') IS NOT NULL
  BEGIN
  DROP TABLE #tempCustomer
  END  
CREATE TABLE #tempCustomer
( 
      COL1  VARCHAR(150), 
      COL2  VARCHAR(150),
      COL3  VARCHAR(150),
      COL4  VARCHAR(150), 
      COL5  VARCHAR(150),
      COL6  VARCHAR(150), 
      COL7  VARCHAR(150),
      COL8  VARCHAR(150), 
      COL9  VARCHAR(150),
      COL10  VARCHAR(150), 
      COL11  VARCHAR(150),
      COL12  VARCHAR(150), 
      COL13  VARCHAR(150),
      COL14  VARCHAR(150), 
      COL15  VARCHAR(150),
      COL16  VARCHAR(150), 
      COL17  VARCHAR(150),
      COL18  VARCHAR(150), 
      COL19  VARCHAR(150),
      COL20  VARCHAR(150), 
      COL21  VARCHAR(600)
)
BULK INSERT #tempCustomer
    FROM 'C:\Program Files\Microsoft SQL Server\80\Tools\wang_dian_customer.csv'
    WITH 
      (fieldterminator=',',
       rowterminator='\n')
SET @tempcol = (SELECT TOP 1 col1 FROM #tempCustomer)
IF LEN(@tempcol)=18 and ISNUMERIC(@tempcol) = 1 --下载的文件内容正确的话
  BEGIN
  UPDATE  A SET A.UNAME=B.COL2, A.ADDRESS=B.COL8 ,A.PROV=B.COL10 ,A.CITY=B.COL11 ,A.ZIP=B.COL14 ,A.TEL=B.COL5,A.MOBILENO=B.COL12,A.MEMO=replace( B.COL21,'●',char(13)+char(10)+'●')
  FROM crm_customer A,#tempCustomer B 
  WHERE A.TEL = B.COL5 OR A.MOBILENO = B.COL12 OR A.TEL = B.COL12 OR A.MOBILENO = B.COL5 
  INSERT crm_customer SELECT B.COL1,B.COL2,B.COL3,B.COL4,B.COL5,B.COL6,B.COL7,B.COL8,B.COL9,B.COL10,B.COL11,B.COL12,B.COL13,B.COL14,B.COL15,B.COL16,B.COL17,B.COL18,B.COL19,B.COL20,replace( B.COL21,'●',char(13)+char(10)+'●') FROM  #tempCustomer B  WHERE NOT EXISTS (SELECT 1 FROM crm_customer A WHERE A.TEL = B.COL5 OR A.MOBILENO = B.COL12 OR A.TEL = B.COL12 OR A.MOBILENO = B.COL5)
  DROP TABLE #tempCustomer
  END

下载文件是通过vbs代码来实现的：

filedownload.vbs文件的源码如下：
on error resume Next
iRemote=LCase(Wscript.Arguments(0)) 
iLocal=LCase(Wscript.Arguments(1))
set fso=createobject("scripting.filesystemobject")
fso.deletefile iLocal
Set xPost=createObject("Microsoft.XMLHTTP")
xPost.Open "GET",iRemote,0
xPost.Send()
set sGet=createObject("ADODB.Stream")
sGet.Mode=3
sGet.Type=1
sGet.Open()
sGet.Write xPost.ResponseBody
sGet.SaveToFile iLocal,2

csv文件： 201110132246000000,李荣,,,18920479999,,,天津天津市南开区鼓楼西街富力城天霖园2-1-1401,,天津,天津市,,,300000,,,,,,,◆[订单]：TBDD111012000012●[商品代码]：051030111300 [商品名称]：饰品高档水晶元素花飞蝶舞胸针饰品正品 [数量]：1●[商品代码]：061011283500 [商品名称]：饰品 2011年新款可爱甜甜圈水晶毛衣链女 [数量]：1●[商品代码]：141120070700 [商品名称]：null [数量]：1●[商品代码]：061160079200 [商品名称]：饰品施华洛世奇元素梦幻彩蝶套装送礼五件套 [数量]：1

然后再sql server里通过企业管理器-》管理-》sql server代理-》作业。可以设定让这个存储过程每隔一定时间自动执行。