簡(jiǎn)介這份資源面向金蝶K3 WISE的二次開發(fā)與運(yùn)維人員提供基礎(chǔ)資料同步所需的SQL語句集合用于解決ERP系統(tǒng)中職員、物料、客戶、供應(yīng)商、計(jì)量單位、倉庫等主數(shù)據(jù)在數(shù)據(jù)庫層面的同步與維護(hù)問題適合具備一定SQL基礎(chǔ)、需要批量處理或跨系統(tǒng)對(duì)接基礎(chǔ)資料的開發(fā)者與實(shí)施顧問。壓縮包為rar格式共15個(gè)文件全部為sql腳本整體約30KB按業(yè)務(wù)對(duì)象分別封裝為同步存儲(chǔ)過程涵蓋物料、客戶、供應(yīng)商、職員、部門、倉庫、計(jì)量單位及其對(duì)應(yīng)類別并附有輔助表同步腳本結(jié)構(gòu)清晰、便于按需調(diào)用。目前已有1348人學(xué)習(xí)下載說明該方案在實(shí)際項(xiàng)目中具備一定參考價(jià)值。讀者可直接獲取各基礎(chǔ)資料的同步邏輯與存儲(chǔ)過程實(shí)現(xiàn)借鑒其字段映射、類別關(guān)聯(lián)與增量同步思路快速應(yīng)用到自己的K3 WISE數(shù)據(jù)集成或接口開發(fā)場(chǎng)景中減少重復(fù)編寫與調(diào)試成本。1. K3 wise 基礎(chǔ)資料同步 sql 語句從手工導(dǎo)表到可復(fù)現(xiàn)的增量同步金蝶 K3 wise 的基礎(chǔ)資料同步是很多制造業(yè)信息化崗位繞不開的日常。物料、客戶、供應(yīng)商、部門、倉庫這幾張表一旦 ERP 和 MES、WMS、SRM 之間對(duì)不上下游單據(jù)就會(huì)大面積報(bào)錯(cuò)。我見過最常見的做法是從 K3 里導(dǎo)出 Excel手工改字段名再導(dǎo)進(jìn)目標(biāo)庫——一次兩次還行上了幾十個(gè)物料分類、上千條客戶檔案之后這套流程必然翻車。真正能長(zhǎng)期跑下去的方案是用 sql 語句把 K3 wise 的基礎(chǔ)資料按增量方式同步出來。核心思路不復(fù)雜K3 wise 的賬套數(shù)據(jù)存在 SQL Server 里基礎(chǔ)資料表有相對(duì)固定的主鍵和修改時(shí)間字段只要抓住「新增」和「變更」兩個(gè)信號(hào)就能用一條查詢語句把差異數(shù)據(jù)撈出來再落到中間表或目標(biāo)庫。適合誰適合手里有 K3 wise 賬套讀權(quán)限、又需要把主數(shù)據(jù)往其他系統(tǒng)推的運(yùn)維和開發(fā)。下面把我實(shí)際用過的表結(jié)構(gòu)、查詢語句、增量判斷和踩坑點(diǎn)拆開講。2. K3 wise 基礎(chǔ)資料的表結(jié)構(gòu)與同步字段映射2.1 先搞清楚基礎(chǔ)資料落在哪幾張表K3 wise 的賬套庫是標(biāo)準(zhǔn) SQL Server 結(jié)構(gòu)基礎(chǔ)資料不是一張大寬表而是「主表 多語言表 輔助表」的組合。物料是最典型的例子t_ICItem存物料主體信息t_ICItemMaterial存物料的物控屬性計(jì)量單位、輔助屬性又分散在別的表里?？蛻艉凸?yīng)商相對(duì)簡(jiǎn)單主表分別是t_Organization里按FType區(qū)分或者獨(dú)立的t_Supplier。我一般先跑一遍元數(shù)據(jù)查詢確認(rèn)當(dāng)前賬套里到底有哪些基礎(chǔ)資料表而不是憑記憶寫死表名。不同版本、不同補(bǔ)丁的 K3 wise表名和字段會(huì)有細(xì)微差異這一步能省掉后面大量「字段不存在」的報(bào)錯(cuò)。-- 查看賬套庫中所有以 t_ 開頭、和基礎(chǔ)資料相關(guān)的表 SELECT name, create_date, modify_date FROM sys.tables WHERE name LIKE t[_]% AND (name LIKE %Item% OR name LIKE %Organization% OR name LIKE %Supplier% OR name LIKE %Department% OR name LIKE %Stock%) ORDER BY name;這段查詢的作用是列出候選表sys.tables是 SQL Server 的系統(tǒng)視圖create_date和modify_date能幫你判斷哪些表是賬套初始化時(shí)就有的、哪些是后來啟用模塊才生成的。參數(shù)上沒什么可調(diào)的唯一要注意的是如果你連的是只讀賬號(hào)sys.tables一般也能查但個(gè)別加固過的環(huán)境會(huì)限制系統(tǒng)視圖訪問那就退回到用INFORMATION_SCHEMA.TABLES。2.2 主鍵、編碼、名稱和修改時(shí)間字段怎么認(rèn)基礎(chǔ)資料同步最怕字段對(duì)不上。K3 wise 的字段命名有強(qiáng)規(guī)律FItemID是內(nèi)部主鍵整型自增跨表關(guān)聯(lián)全靠它FNumber是編碼業(yè)務(wù)上唯一FName是名稱但注意它常常不在主表而在帶_Language后綴的多語言表里比如t_ICItem_L。修改時(shí)間字段通常是FModifyDate或FLastModifyDate但并不是每張表都有。我習(xí)慣先對(duì)目標(biāo)表做一次字段盤點(diǎn)把「主鍵、編碼、名稱、修改時(shí)間」四類字段確認(rèn)清楚再?zèng)Q定同步語句怎么寫。下面這段查詢把某張表的字段、類型、是否可空一次性拉出來-- 盤點(diǎn)指定基礎(chǔ)資料表的字段結(jié)構(gòu) SELECT c.name AS column_name, t.name AS data_type, c.max_length, c.is_nullable FROM sys.columns c JOIN sys.types t ON c.user_type_id t.user_type_id WHERE c.object_id OBJECT_ID(t_ICItem) ORDER BY c.column_id;OBJECT_ID(t_ICItem)把表名轉(zhuǎn)成對(duì)象 IDsys.columns和sys.types聯(lián)查拿到字段類型。max_length對(duì)nvarchar是字節(jié)數(shù)實(shí)際字符數(shù)要除以 2這個(gè)細(xì)節(jié)在拼接目標(biāo)庫建表語句時(shí)很關(guān)鍵不然容易把名稱字段截?cái)唷Ｈ绻P點(diǎn)發(fā)現(xiàn)沒有FModifyDate那增量同步就得換策略比如用FItemID最大值做水位線或者干脆依賴 K3 的審計(jì)日志。2.3 字段映射表K3 字段到目標(biāo)庫的對(duì)應(yīng)關(guān)系同步不是把 K3 字段原樣搬過去目標(biāo)系統(tǒng)往往有自己的命名。我一般維護(hù)一張映射表把源字段、目標(biāo)字段、轉(zhuǎn)換規(guī)則寫清楚避免每次改需求都去翻代碼。下面是我給物料同步常用的一份映射示例| K3 源字段 | 目標(biāo)字段 | 類型 | 轉(zhuǎn)換規(guī)則 | |-----------|----------|------時(shí)|----------| | FItemID | item_id | int | 直接映射作為目標(biāo)主鍵 | | FNumber | item_code | varchar(80) | 去空格轉(zhuǎn)大寫 | | FName來自 _L 表 | item_name | nvarchar(200) | 取 FLCID2052 的中文行 | | FModel | spec | nvarchar(200) | 空值轉(zhuǎn)空串 | | FUnitID | unit_id | int | 關(guān)聯(lián)計(jì)量單位表換算 | | FModifyDate | updated_at | datetime | 直接映射作為增量水位 |這張表的價(jià)值在于當(dāng)目標(biāo)庫字段調(diào)整時(shí)你只改映射不動(dòng)同步邏輯。注意FName來自多語言表FLCID2052是簡(jiǎn)體中文的語言標(biāo)識(shí)這個(gè)值在 K3 wise 里是固定的但如果你賬套啟用了多語言且默認(rèn)語言不是中文就要按實(shí)際FLocaleID調(diào)整。3. 用 sql 語句寫出可復(fù)現(xiàn)的基礎(chǔ)資料同步查詢3.1 全量同步語句一次把物料主數(shù)據(jù)撈干凈全量同步是增量同步的基礎(chǔ)先把語句寫對(duì)再談增量。物料全量查詢要解決三個(gè)問題主表和多語言表關(guān)聯(lián)、過濾掉禁用和刪除的記錄、字段類型對(duì)齊。K3 wise 里FDeleted為 1 表示已刪除FUseState或類似字段表示啟用狀態(tài)這兩個(gè)條件不加同步過去的就是一堆廢數(shù)據(jù)。-- 物料基礎(chǔ)資料全量查詢簡(jiǎn)體中文 SELECT i.FItemID AS item_id, i.FNumber AS item_code, l.FName AS item_name, i.FModel AS spec, i.FUnitID AS unit_id, i.FModifyDate AS updated_at FROM t_ICItem i LEFT JOIN t_ICItem_L l ON i.FItemID l.FItemID AND l.FLCID 2052 WHERE i.FDeleted 0 AND i.FUseState 1 ORDER BY i.FItemID;邏輯上LEFT JOIN保證即使多語言表缺行主表記錄也不會(huì)丟FLCID 2052鎖定中文名稱FDeleted 0和FUseState 1過濾無效數(shù)據(jù)。參數(shù)方面FUseState的取值在不同版本里可能是 0/1 或 1/2跑之前先用SELECT DISTINCT FUseState FROM t_ICItem確認(rèn)一下別照抄。ORDER BY FItemID不是必須的但加上之后結(jié)果穩(wěn)定方便做數(shù)據(jù)比對(duì)。3.2 增量同步語句靠修改時(shí)間水位線只取差異全量每次幾萬條天天跑不現(xiàn)實(shí)。增量同步的核心是水位線記錄上次同步到的最大FModifyDate下次只取比它大的記錄。這里有個(gè)血淚經(jīng)驗(yàn)——K3 wise 的FModifyDate精度到秒如果同一秒內(nèi)改了多條可能漏數(shù)據(jù)所以水位線要回退幾秒做補(bǔ)償。-- 增量同步取上次水位線之后的變更記錄 DECLARE last_sync DATETIME 2024-01-01 00:00:00; -- 替換為實(shí)際水位線 SELECT i.FItemID, i.FNumber, l.FName, i.FModel, i.FUnitID, i.FModifyDate FROM t_ICItem i LEFT JOIN t_ICItem_L l ON i.FItemID l.FItemID AND l.FLCID 2052 WHERE i.FDeleted 0 AND i.FModifyDate DATEADD(SECOND, -5, last_sync) ORDER BY i.FModifyDate;DATEADD(SECOND, -5, last_sync)就是回退 5 秒的補(bǔ)償代價(jià)是可能重復(fù)取到少量數(shù)據(jù)所以目標(biāo)庫寫入必須用MERGE或「先刪后插」保證冪等。last_sync的實(shí)際值應(yīng)該從一張同步日志表里讀而不是寫死在語句里。我一般建一張etl_sync_log表每次同步成功后把本次最大FModifyDate寫進(jìn)去下次讀出來用。3.3 把查詢結(jié)果落到中間表MERGE 語句的寫法查出來只是第一步落到目標(biāo)庫才是同步。SQL Server 里我首選MERGE一條語句搞定「存在則更新、不存在則插入」。但MERGE有個(gè)著名坑并發(fā)下可能死鎖所以要么加HOLDLOCK要么在低峰期跑。-- 將增量數(shù)據(jù)合并進(jìn)目標(biāo)中間表 MERGE INTO dw_item AS tgt USING ( SELECT i.FItemID AS item_id, i.FNumber AS item_code, l.FName AS item_name, i.FModel AS spec, i.FUnitID AS unit_id, i.FModifyDate AS updated_at FROM t_ICItem i LEFT JOIN t_ICItem_L l ON i.FItemID l.FItemID AND l.FLCID 2052 WHERE i.FDeleted 0 AND i.FModifyDate DATEADD(SECOND, -5, last_sync) ) AS src ON tgt.item_id src.item_id WHEN MATCHED THEN UPDATE SET tgt.item_code src.item_code, tgt.item_name src.item_name, tgt.spec src.spec, tgt.unit_id src.unit_id, tgt.updated_at src.updated_at WHEN NOT MATCHED THEN INSERT (item_id, item_code, item_name, spec, unit_id, updated_at) VALUES (src.item_id, src.item_code, src.item_name, src.spec, src.unit_id, src.updated_at);MERGE的ON條件必須是目標(biāo)表的唯一鍵這里用item_id。WHEN MATCHED處理更新WHEN NOT MATCHED處理插入。注意MERGE語句末尾的分號(hào)不能省這是語法要求。如果目標(biāo)表還有「源端已刪除」的同步需求得再加一個(gè)WHEN NOT MATCHED BY SOURCE THEN DELETE但那會(huì)物理刪除目標(biāo)數(shù)據(jù)慎用我一般改成軟刪除標(biāo)記。3.4 客戶和供應(yīng)商同步的差異點(diǎn)客戶和供應(yīng)商在 K3 wise 里共用t_Organization表靠FType區(qū)分FType1是客戶FType2是供應(yīng)商具體取值以賬套為準(zhǔn)跑之前先SELECT DISTINCT FType FROM t_Organization確認(rèn)。同步語句結(jié)構(gòu)和物料類似但要注意t_Organization本身可能就帶名稱字段不一定需要關(guān)聯(lián)多語言表。-- 客戶基礎(chǔ)資料增量同步 SELECT o.FItemID AS org_id, o.FNumber AS org_code, o.FName AS org_name, o.FType AS org_type, o.FModifyDate AS updated_at FROM t_Organization o WHERE o.FDeleted 0 AND o.FType 1 AND o.FModifyDate DATEADD(SECOND, -5, last_sync) ORDER BY o.FModifyDate;這里FType 1是客戶過濾條件供應(yīng)商改成對(duì)應(yīng)值即可。和物料最大的差異是t_Organization的FName通常直接在主表不需要_L關(guān)聯(lián)少一次 JOIN性能更好。但如果你賬套啟用了多語言且客戶名稱需要按語言取還是得去t_Organization_L里拿。4. 同步任務(wù)落地調(diào)度、日志與冪等設(shè)計(jì)4.1 用 SQL Server 代理作業(yè)定時(shí)跑語句寫好了得讓它自動(dòng)跑。SQL Server 代理作業(yè)是最省事的方式不用額外部署調(diào)度框架。建作業(yè)的步驟在 SSMS 里展開「SQL Server 代理」→ 右鍵「作業(yè)」→「新建作業(yè)」添加一個(gè) T-SQL 步驟把上面的增量同步語句貼進(jìn)去再配一個(gè)每天凌晨或每 15 分鐘執(zhí)行一次的調(diào)度。作業(yè)步驟里我一般把「讀水位線 → 執(zhí)行 MERGE → 更新水位線」寫成一個(gè)事務(wù)避免中途失敗導(dǎo)致水位線錯(cuò)亂。下面是一個(gè)把三步串起來的骨架BEGIN TRANSACTION; DECLARE last_sync DATETIME; SELECT last_sync last_value FROM etl_sync_log WHERE table_name t_ICItem; -- 這里放 MERGE 語句略見 3.3 UPDATE etl_sync_log SET last_value (SELECT MAX(FModifyDate) FROM t_ICItem WHERE FDeleted 0), updated_at GETDATE() WHERE table_name t_ICItem; COMMIT TRANSACTION;事務(wù)保證三步要么全成、要么全回滾。etl_sync_log表至少要有table_name、last_value、updated_at三個(gè)字段。注意MAX(FModifyDate)取的是全表最大值不是本次增量結(jié)果的最大值——這樣即使本次沒取到數(shù)據(jù)水位線也不會(huì)倒退。4.2 冪等重復(fù)跑不會(huì)產(chǎn)生臟數(shù)據(jù)調(diào)度任務(wù)最怕重復(fù)執(zhí)行。網(wǎng)絡(luò)抖動(dòng)、作業(yè)重試、人工手動(dòng)補(bǔ)跑都可能讓同一批數(shù)據(jù)被處理兩次。冪等的關(guān)鍵在目標(biāo)表item_id必須是唯一鍵或主鍵MERGE的ON條件命中它重復(fù)跑只會(huì)更新不會(huì)插入重復(fù)行。如果目標(biāo)表沒有唯一約束那就得在寫入前先按主鍵去重。SQL 語句去重查詢是熱詞里高頻出現(xiàn)的需求這里給一個(gè)通用寫法-- 按主鍵去重只保留每個(gè) item_id 最新的一條 SELECT item_id, item_code, item_name, spec, unit_id, updated_at FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY updated_at DESC) AS rn FROM dw_item_staging ) t WHERE rn 1;ROW_NUMBER()按item_id分組、按updated_at倒序編號(hào)rn 1就是每組最新那條。這個(gè)模式在清洗 csv 數(shù)據(jù)、去重查詢場(chǎng)景里通用把PARTITION BY換成你的業(yè)務(wù)主鍵即可。4.3 同步日志表怎么設(shè)計(jì)才夠用日志表不是可有可無的裝飾出問題時(shí)它是唯一的后悔藥。我一般設(shè)計(jì)成每次同步一行記錄表名、開始時(shí)間、結(jié)束時(shí)間、處理行數(shù)、水位線、狀態(tài)和錯(cuò)誤信息。字段不用多但status和error_msg必須有否則失敗了你只能看到作業(yè)紅了不知道紅在哪。CREATE TABLE etl_sync_log ( id INT IDENTITY(1,1) PRIMARY KEY, table_name VARCHAR(64) NOT NULL, last_value DATETIME NULL, row_count INT DEFAULT 0, status VARCHAR(16) DEFAULT running, error_msg NVARCHAR(1000) NULL, started_at DATETIME DEFAULT GETDATE(), finished_at DATETIME NULL );status用running、success、failed三態(tài)作業(yè)開始時(shí)插一條running結(jié)束時(shí)更新為success或failed。row_count記錄本次處理行數(shù)連續(xù)幾次為 0 就要警惕是不是水位線卡住了。error_msg存ERROR_MESSAGE()的內(nèi)容排查時(shí)直接看這一列。5. 基礎(chǔ)資料同步的避坑與排查清單5.1 現(xiàn)象同步后名稱全是 NULL原因多語言表關(guān)聯(lián)條件寫錯(cuò)或者FLCID用了默認(rèn)值但賬套實(shí)際語言不是 2052。K3 wise 的多語言表里同一個(gè)FItemID可能有多行對(duì)應(yīng)不同語言FLCID不對(duì)就關(guān)聯(lián)不上。解決先跑SELECT DISTINCT FLCID FROM t_ICItem_L看實(shí)際有哪些語言標(biāo)識(shí)再用正確的值。如果目標(biāo)只要中文就鎖定中文那一行如果賬套只有一種語言FLCID可能是別的值別照抄 2052。5.2 現(xiàn)象增量同步漏數(shù)據(jù)明明改了卻沒同步過來原因FModifyDate精度到秒同一秒內(nèi)多條修改水位線取最大值后同秒的其他記錄被跳過。另外有些基礎(chǔ)資料的修改不會(huì)更新FModifyDate比如只改了多語言表里的名稱。解決水位線回退幾秒做補(bǔ)償見 3.2并且把多語言表的修改也納入判斷。如果多語言表沒有修改時(shí)間字段那就只能定期做一次全量比對(duì)或者監(jiān)聽 K3 的審計(jì)日志。5.3 現(xiàn)象MERGE 語句報(bào)「違反主鍵約束」原因源數(shù)據(jù)里同一個(gè)item_id出現(xiàn)了多行MERGE的USING子查詢返回了重復(fù)主鍵導(dǎo)致目標(biāo)表插入沖突。常見于多語言表關(guān)聯(lián)后沒去重或者t_ICItem本身有重復(fù)FItemID極少但存在。解決在USING子查詢里先用ROW_NUMBER()去重或者加GROUP BY。跑之前先SELECT FItemID, COUNT(*) FROM t_ICItem GROUP BY FItemID HAVING COUNT(*) 1確認(rèn)源端有沒有重復(fù)。5.4 現(xiàn)象作業(yè)跑著跑著 CPU 占用飆升原因增量查詢沒走索引FModifyDate上沒有索引每次全表掃描。數(shù)據(jù)量大了之后一條查詢能把 CPU 打滿進(jìn)程 sql 語句 cpu 占用 oracle 這類問題在 SQL Server 上同樣存在。解決在FModifyDate上建非聚集索引或者建(FDeleted, FModifyDate)復(fù)合索引。建索引前先看執(zhí)行計(jì)劃確認(rèn)瓶頸在掃描還是排序。注意 K3 wise 的賬套庫不建議隨意加索引可能影響 ERP 本身性能最好在只讀副本或同步庫上操作。5.5 現(xiàn)象目標(biāo)庫字段被截?cái)嗝Q只剩一半原因K3 wise 的FName是nvarchar目標(biāo)庫建成了varchar中文按字節(jié)算長(zhǎng)度不夠或者max_length換算時(shí)忘了除以 2。解決目標(biāo)庫名稱字段統(tǒng)一用nvarchar長(zhǎng)度至少是源字段的兩倍余量。建表前用 2.2 的字段盤點(diǎn)語句確認(rèn)源字段實(shí)際長(zhǎng)度別憑感覺寫。6. 把同步做成可驗(yàn)證的日常習(xí)慣同步做完不是終點(diǎn)能驗(yàn)證才算落地。我現(xiàn)在的習(xí)慣是每次同步后跑一條對(duì)賬查詢比對(duì)源端和目標(biāo)端的記錄數(shù)、最大修改時(shí)間、抽樣幾條名稱是否一致。對(duì)賬語句不用復(fù)雜關(guān)鍵是固定下來、每次都跑。-- 源端與目標(biāo)端對(duì)賬記錄數(shù)和最大修改時(shí)間 SELECT source AS side, COUNT(*) AS cnt, MAX(FModifyDate) AS max_mod FROM t_ICItem WHERE FDeleted 0 UNION ALL SELECT target, COUNT(*), MAX(updated_at) FROM dw_item;兩邊cnt差距超過閾值比如 1%或者max_mod明顯落后就說明同步有問題得去查日志。這個(gè)對(duì)賬我一般做成一個(gè)獨(dú)立的作業(yè)步驟同步完自動(dòng)跑結(jié)果寫進(jìn)日志表。再進(jìn)階一點(diǎn)可以把同步語句參數(shù)化用一張配置表存「表名、源表、目標(biāo)表、水位線字段、過濾條件」這樣新增一張基礎(chǔ)資料表只要插一行配置不用改代碼。我吃過硬編碼的虧——客戶檔案同步邏輯復(fù)制了五份改一個(gè)字段要改五處后來統(tǒng)一成配置驅(qū)動(dòng)才消停。最后說個(gè)我自己的教訓(xùn)別在業(yè)務(wù)高峰期跑全量同步。K3 wise 的賬套庫和生產(chǎn)系統(tǒng)共用資源一條大查詢能把 ERP 拖慢車間掃碼都卡。我現(xiàn)在所有全量任務(wù)都排在凌晨增量任務(wù)控制在秒級(jí)返回跑之前先看執(zhí)行計(jì)劃確認(rèn)走索引。同步這件事穩(wěn)比快重要希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取