寫了一個常規性生成merge 的小腳本

来源:http://www.cnblogs.com/Gin-23333/archive/2016/05/06/5466442.html
-Advertisement-
Play Games

現在使用資料庫來寫存儲過程，動不動參數就會用到xml ，當然羅，優勢也很明顯，參數相對固定，而且靈活，如果要修改或者什麼的，中間介面層也不需要做變化，只需要修改封裝的存儲過程以及程式傳參就ok了。 隨著時間慢慢過，有時候就有一個存儲過程，一個xml 來應對整個表的新增，修改，刪除的情況了。而對於這個 ...


現在使用資料庫來寫存儲過程，動不動參數就會用到xml ，當然羅，優勢也很明顯，參數相對固定，而且靈活，如果要修改或者什麼的，中間介面層也不需要做變化，只需要修改封裝的存儲過程以及程式傳參就ok了。

隨著時間慢慢過，有時候就有一個存儲過程，一個xml 來應對整個表的新增，修改，刪除的情況了。而對於這個情況，我個人比較喜歡使用 Merge關鍵字來處理。但是如果表裡面的列很多，那麼複製黏貼啊之類的機械動作就會很多，而且沒有什麼價值。所以我就寫了一個小腳本，應對了使用xml 來做表的增刪改的作用

首先我先創建一個表

CREATE TABLE employee(
ID INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(50),
age INT,
birthdate DATE,
salary MONEY
)

然後我準備使用這個xml 來進行對應寫入

DECLARE @employee XML='
<root>
    <employee Action="1">  <!--這個Action 代表動作，1 新增 2 修改 3 刪除 這樣來控制比較靈活，不需要每次都一大段-->
        <name>AAA</name>
        <age>27</age>
        <birthdate>1989-01-02</birthdate>
        <salary>1200</salary>
    </employee>
    <employee Action="1">
        <name>BBB</name>
        <age>23</age>
        <birthdate>1994-01-02</birthdate>
        <salary>2200</salary>
    </employee>
</root>
'

然後是生成的腳本。通常解析xml 會有2種的解析方法，一種是直接用openxml 來進行解析，一種是使用 xml.nodes 的函數進行取值，這裡我兩種都可以進行一個簡單處理生成

 1 DECLARE @TableName VARCHAR(50) = 'employee',       
 2         @XMLType TINYINT = 1,  --1 使用with 格式, 2 使用nodes 格式        
 3         @Path NVARCHAR(max) = 'root/employee',            
 4         @HasAction BIT = 1 --0 沒有動作 1 包含動作        
 5 
 6 DECLARE @Columns NVARCHAR(MAX),    --通用列的串
 7         @FilterColumns NVARCHAR(max), --過濾外鍵，主鍵的列
 8         @On NVARCHAR(100), --自動生成主鍵去匹配
 9         @Sql NVARCHAR(MAX)
10 
11 SELECT @Columns = STUFF((
12 SELECT ',' + name
13     FROM sys.columns 
14         WHERE object_id = OBJECT_ID(@TableName)
15         ORDER BY column_id
16         FOR XML PATH('')),1,1,''),
17 @FilterColumns = STUFF((
18 SELECT ',' + name
19     FROM sys.columns 
20         WHERE object_id = OBJECT_ID(@TableName)
21            AND is_computed = 0
22               AND is_identity = 0               
23         ORDER BY column_id
24         FOR XML PATH('')),1,1,''),
25 @On = STUFF((
26 SELECT 'AND TAR.' + c.name + ' = SOUR.' + c.name
27     FROM sys.indexes a
28         INNER JOIN sys.index_columns b ON a.object_id = b.object_id 
29         INNER JOIN sys.columns c ON c.object_id = b.object_id AND b.column_id = c.column_id
30     WHERE a.object_id = OBJECT_ID(@TableName)
31       AND a.is_primary_key = 1),1,4,'')
32 
33     
34 
35 SELECT @Sql = ';WITH SOUR AS(
36 SELECT '+ CASE WHEN @XMLType = 1 THEN REPLACE(@Columns ,',',CHAR(10) + REPLICATE(CHAR(9),2) + ',') 
37                WHEN @XMLType = 2 THEN STUFF((SELECT CHAR(10) + ',t.c.value(''(' + a.name + '/text())[1]'',''' + TYPE_NAME(user_type_id) +  CASE WHEN a.system_type_id IN (167,175) THEN '(' + CASE WHEN a.max_length = -1 THEN 'max' ELSE RTRIM(a.max_length) END+ ')'
38                          WHEN a.system_type_id IN (231,239) THEN '(' + CASE WHEN a.max_length = -1 THEN 'max' ELSE RTRIM(a.max_length/2) END + ')'
39                          WHEN a.system_type_id IN (59,106,108) THEN '(' + RTRIM(a.max_length) + ',' + RTRIM(a.scale) + ')'
40                          ELSE ''
41                          END + ''') AS ' + a.name
42                         FROM sys.columns a
43                             WHERE object_id = OBJECT_ID(@TableName)
44                             ORDER BY column_id
45                             FOR XML PATH('')),1,2,'')    
46           ELSE '' END
47         + CASE WHEN @XMLType = 1 AND @HasAction = 1 THEN CHAR(10)+ REPLICATE(CHAR(9),2) + ',[Action]' 
48                WHEN @XMLType = 2 AND @HasAction = 1 THEN CHAR(10)+ REPLICATE(CHAR(9),2) + ',t.c.value(''@Action'',''tinyint'') [Action]' 
49           ELSE '' END 
50     + '
51     FROM ' + CASE @XMLType WHEN 1 THEN ' OPENXML(@XmlInt,''' + @Path + ''',3)
52                     WITH(' + STUFF((SELECT CHAR(10)+ REPLICATE(CHAR(9),6) + ',' + a.name + ' ' + UPPER(b.name) + CASE WHEN a.system_type_id IN (167,175,231,239,108) THEN '(' + CASE WHEN a.max_length = -1 THEN 'MAX' ELSE  RTRIM(a.max_length) END + ')'
53                                                                   WHEN a.system_type_id IN (59,106,108) THEN '(' + RTRIM(a.precision) + ',' + RTRIM(a.scale)+ ')'
54                                                              ELSE '' END + ' ''' + a.name + ''''
55                                 FROM sys.columns a
56                                     INNER JOIN sys.systypes b ON a.system_type_id = b.xtype AND b.status = 0
57                                     WHERE object_id = OBJECT_ID(@TableName)
58                                       AND a.is_computed = 0
59                                       ORDER BY column_id
60                                       FOR XML PATH ('')
61                                       ),1,8,'') + 
62                             + CASE WHEN @HasAction = 1 THEN CHAR(10)+ REPLICATE(CHAR(9),6) + ',[Action] tinyint ''@Action'')' ELSE ')' END 
63                             WHEN 2 THEN ' @' + @TableName + '.nodes('''+@Path+''') as t(c)'
64                             ELSE '' END 
65              + '),
66 TAR AS( SELECT ' + REPLACE(@Columns,',',CHAR(10) + REPLICATE(CHAR(9),2) + ',') + '
67         FROM ' + @TableName + ')
68 MERGE TAR
69 USING SOUR
70 ON '+@On+'
71 WHEN NOT MATCHED ' + CASE WHEN @HasAction = 1 THEN ' AND SOUR.[Action] = 1 ' ELSE '' END + '
72             THEN INSERT(' + @FilterColumns + ')' + CHAR(10) + REPLICATE(CHAR(9),5) + ' VALUES (SOUR.' + REPLACE(@FilterColumns,',',',SOUR.') + ')
73 WHEN MATCHED ' + CASE WHEN @HasAction = 1 THEN ' AND SOUR.[Action] = 2 ' ELSE '' END + ' THEN UPDATE SET ' 
74 + STUFF((    SELECT ',' + CHAR(10) + REPLICATE(CHAR(9),5) +  'TAR.[' + name + ']= SOUR.[' + name + ']'
75         FROM sys.columns a
76             WHERE object_id = OBJECT_ID(@TableName)
77               AND is_computed = 0
78               AND is_identity = 0
79               AND NOT EXISTS(SELECT * FROM sys.foreign_key_columns WHERE parent_object_id = a.object_id AND parent_column_id = a.column_id)
80                 ORDER BY column_id
81             FOR XML PATH('')
82     ),1,6,'') + '
83     '+ CASE WHEN @HasAction = 1 THEN ' WHEN MATCHED  AND SOUR.[Action] = 3 ' ELSE 
84     'WHEN MATCHED BY SOURCE ' END + ' THEN Delete;'
85 PRINT @Sql
View Code

(因為偷懶，所以使用的openxml 裡面的那個 sp_xml_preparedocument 這裡我是沒有寫的)(*^__^*) 嘻嘻……

然後看下生成的情況，這個是使用xml.nodes 來生成的

 1 ;WITH SOUR AS(
 2 SELECT t.c.value('(ID/text())[1]','int') AS ID
 3 ,t.c.value('(name/text())[1]','nvarchar(50)') AS name
 4 ,t.c.value('(age/text())[1]','int') AS age
 5 ,t.c.value('(birthdate/text())[1]','date') AS birthdate
 6 ,t.c.value('(salary/text())[1]','money') AS salary
 7         ,t.c.value('@Action','tinyint') [Action]
 8     FROM  @employee.nodes('root/employee') as t(c)),
 9 TAR AS( SELECT ID
10         ,name
11         ,age
12         ,birthdate
13         ,salary
14         FROM employee)
15 MERGE TAR
16 USING SOUR
17 ON TAR.ID = SOUR.ID
18 WHEN NOT MATCHED  AND SOUR.[Action] = 1 
19             THEN INSERT(name,age,birthdate,salary)
20                      VALUES (SOUR.name,SOUR.age,SOUR.birthdate,SOUR.salary)
21 WHEN MATCHED  AND SOUR.[Action] = 2  THEN UPDATE SET     TAR.[name]= SOUR.[name],
22                     TAR.[age]= SOUR.[age],
23                     TAR.[birthdate]= SOUR.[birthdate],
24                     TAR.[salary]= SOUR.[salary]
25      WHEN MATCHED  AND SOUR.[Action] = 3  THEN Delete;
xml.nodes

這個是使用openxml來生成的

;WITH SOUR AS(
SELECT ID
        ,name
        ,age
        ,birthdate
        ,salary
        ,[Action]
    FROM  OPENXML(@XmlInt,'root/employee',3)
                    WITH(ID INT 'ID'
                        ,name NVARCHAR(100) 'name'
                        ,age INT 'age'
                        ,birthdate DATE 'birthdate'
                        ,salary MONEY 'salary'
                        ,[Action] tinyint '@Action')),
TAR AS( SELECT ID
        ,name
        ,age
        ,birthdate
        ,salary
        FROM employee)
MERGE TAR
USING SOUR
ON TAR.ID = SOUR.ID
WHEN NOT MATCHED  AND SOUR.[Action] = 1 
            THEN INSERT(name,age,birthdate,salary)
                     VALUES (SOUR.name,SOUR.age,SOUR.birthdate,SOUR.salary)
WHEN MATCHED  AND SOUR.[Action] = 2  THEN UPDATE SET     TAR.[name]= SOUR.[name],
                    TAR.[age]= SOUR.[age],
                    TAR.[birthdate]= SOUR.[birthdate],
                    TAR.[salary]= SOUR.[salary]
     WHEN MATCHED  AND SOUR.[Action] = 3  THEN Delete;
openxml

恩~然後就可放進去執行啦~~

這裡只是一個很基本的用法。有幾點要說明的

1、Xml的名稱我預設和表名一致，有需要請改動

2、On的匹配模型我是使用主鍵來進行對應

其它如果有什麼問題請告訴我補充~

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • android scrollview主要的問題
    項目做多了之後，會發現其實 ScrollView嵌套ListVew或者GridView等很常用，但是你也會發現各種奇怪問題產生。根據個人經驗現在列出常見問題以及代碼最少最簡單的解決方法。 問題一 ： 嵌套在 ScrollView的 ListVew數據顯示不全，我遇到的是最多只顯示兩條已有的數據。 解 ...
  • 【Swift學習】Swift編程之旅---構造方法（十八）
    初始化是為了使用某個類、結構體或枚舉類型的實例而進行的準備過程。這個過程包括為每個存儲的屬性設置一個初始值，然後執行新實例所需的任何其他設置或初始化。 初始化是通過定義構造器（Initializers）來實現的，這些構造器可以看做是用來創建特定類型實例的特殊方法。與 Objective-C 中的構造 ...
  • Android之Handler探索
    Handler背景理解： Handler被最多的使用在了更新UI線程中，但是，這個方法具體是什麼樣的呢？我在這篇博文中先領著大家認識一下什麼是handler以及它是怎麼樣使用在程式中，起著什麼樣的作用。 示例說明： 首先先建立兩個按鈕：一個是start按鈕，作用是開啟整個程式。另一個是終止按鈕end ...
  • Mysql主從備份和SQL語句的備份
    MySQL伺服器的主從配置，本來是一件很簡單的事情，無奈不是從零開始，總是在別人已經安裝好的mysql伺服器之上 ，這就會牽扯到，mysql的版本，啟動文件，等一些問題。 http://www.cnblogs.com/roucheng/p/phpmysql.html 不過沒關係，先問清楚兩點 1、m ...
  • sql server索引功能資料
    無論何時對基礎數據執行插入、更新或刪除操作，SQL Server 資料庫引擎都會自動維護索引。隨著時間的推移，這些修改可能會導致索引中的信息分散在資料庫中（含有碎片）。當索引包含的頁中的邏輯排序（基於鍵值）與數據文件中的物理排序不匹配時，就存在碎片。碎片非常多的索引可能會降低查詢性能，導致應用程式響 ...
  • ORA-00030: User session ID does not exist.
    同事在Toad裡面執行SQL語句時，突然無線網路中斷了，讓我檢查一下具體情況，如下所示（有些信息，用xxx替換,因為是在處理那些歷史歸檔數據，使用的一個特殊用戶，所以可以用下麵SQL找到對應的會話信息）： SQL> SELECT B.USERNAME , 2 B.SID , 3 B.SERIAL# ... ...
  • redis學習筆記
    Redis是一個開源的使用ANSI C語言編寫、支持網路、可基於記憶體亦可持久化的日誌型、Key-Value資料庫，並提供多種語言的API redis是一個key-value存儲系統。和Memcached類似，它支持存儲的value類型相對更多，包括string(字元串)、list(鏈表)、set(集 ...
  • 關係資料庫SQL之基本數據查詢:子查詢、分組查詢、模糊查詢
    前言 上一篇[關係資料庫常用SQL語句語法大全][2]主要是關係型資料庫大體結構，本文細說一下關係型資料庫查詢的SQL語法。 ![SQL數據查詢][1] 語法回顧 SQL查詢語句的順序：SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。SELECT、FROM是必須 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類