-- 步骤1：启用Ad Hoc分布式查询（若未启用）
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
-- 步骤2：创建临时表存储SAP数据
CREATE TABLE #SAPData (
Phone varchar(15) PRIMARY KEY,
CustomerTypeCode varchar(20),
S6 varchar(50),
CompanyName varchar(100),
Name varchar(50),
S4 varchar(50),
S2 varchar(50),
S1 varchar(50),
S3 varchar(50),
S5 varchar(50)
);
-- 步骤3：从SAP服务器提取数据到临时表
INSERT INTO #SAPData (Phone, CustomerTypeCode, S6, CompanyName, Name, S4, S2, S1, S3, S5)
SELECT
LEFT(Telephone, 15) AS Phone,
CASE
WHEN [客户类型] = 'VIP' THEN '703192237850693'
ELSE '703192237846597'
END AS CustomerTypeCode,
CardCode AS S6,
Cardname AS CompanyName,
Name,
FORMAT(近一年总毛利, '0.00') AS S4,
FORMAT(预收款金额, '0.00') AS S2,
FORMAT(应收款, '0.00') AS S1,
FORMAT(全部库存金额, '0.00') AS S3,
等级名称 AS S5
FROM OPENROWSET(
'SQLNCLI',
'Server=192.168.0.229;UID=SAPReader;PWD=Sino2025zyq;Database=SINO_SAP',
'SELECT
p.Telephone, p.CardCode, p.Cardname, p.Name,
prof.[客户类型], prof.近一年总毛利, prof.预收款金额,
prof.应收款, prof.全部库存金额, prof.等级名称
FROM ZY_TB_CustomerPhone p
LEFT JOIN ZY_TB_CustomerProfile prof ON p.CardCode = prof.[客户编号]
WHERE p.CardCode IS NOT NULL
AND p.Cardname IS NOT NULL
AND p.Telephone NOT LIKE ''8441%'''
);
-- 步骤4：更新现有数据
UPDATE c
SET
c.CompanyName = s.CompanyName,
c.S6 = s.S6,
c.CustomerTypeCode = s.CustomerTypeCode,
c.S5 = s.S5,
c.S1 = s.S1,
c.S2 = s.S2,
c.S3 = s.S3,
c.S4 = s.S4
FROM OrderManage.dbo.T_Customer c
JOIN #SAPData s ON c.Phone = s.Phone;
-- 步骤5：插入新增数据（确保ID和GUID不重复）
DECLARE @MaxId bigint;
SELECT @MaxId = ISNULL(MAX(Id), 111100000048210) FROM OrderManage.dbo.T_Customer;
INSERT INTO OrderManage.dbo.T_Customer (
Id, GUID, Domain, CustomerTypeCode, Remark,
S6, CompanyName, Name, Phone, S4, S2, S1, S3, S5
)
SELECT
@MaxId + ROW_NUMBER() OVER(ORDER BY Phone) AS Id,
LOWER(NEWID()) AS GUID,
'ipcc.org' AS Domain,
s.CustomerTypeCode,
'' AS Remark,
s.S6,
s.CompanyName,
s.Name,
s.Phone,
s.S4,
s.S2,
s.S1,
s.S3,
s.S5
FROM #SAPData s
WHERE NOT EXISTS (
SELECT 1
FROM OrderManage.dbo.T_Customer c
WHERE c.Phone = s.Phone
);
-- 步骤6：清理临时表
DROP TABLE #SAPData;
这个代码是再sqlserver中运行的么 但是 OrderManage.dbo.T_Customer 是一个mysql的表 两个数据来源是不一样的
扫一扫
1480
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
