目的:呼叫中心系统(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代理-》作业。可以设定让这个存储过程每隔一定时间自动执行。