寫了一個常規性生成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
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類