专注收集记录技术开发学习笔记、技术难点、解决方案
网站信息搜索 >> 请输入关键词:
您当前的位置: 首页 > Sql Server

写了一个常例性生成merge 的小脚本

发布时间:2010-05-20 14:01:29 文章来源:www.iduyao.cn 采编人员:星星草
写了一个常规性生成merge 的小脚本

现在使用数据库来写存储过程,动不动参数就会用到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的匹配模型我是使用主键来进行对应

其它如果有什么问题请告诉我补充~

 

友情提示:
信息收集于互联网,如果您发现错误或造成侵权,请及时通知本站更正或删除,具体联系方式见页面底部联系我们,谢谢。

其他相似内容:

热门推荐: