你的位置:首页 > 数据库

[数据库]写了一个常规性生成merge 的小脚本

现在使用数据库来写存储过程,动不动参数就会用到

随着时间慢慢过,有时候就有一个存储过程,一个

首先我先创建一个表

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

然后我准备使用这个

DECLARE @employee ='<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>'

然后是生成的脚本。通常解析

 1 DECLARE @TableName VARCHAR(50) = 'employee',     2     @ 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 ',' + name13   FROM sys.columns 14     WHERE object_id = OBJECT_ID(@TableName)15     ORDER BY column_id16     FOR '')),1,1,''),17 @FilterColumns = STUFF((18 SELECT ',' + name19   FROM sys.columns 20     WHERE object_id = OBJECT_ID(@TableName)21      AND is_computed = 022        AND is_identity = 0        23     ORDER BY column_id24     FOR '')),1,1,''),25 @On = STUFF((26 SELECT 'AND TAR.' + c.name + ' = SOUR.' + c.name27   FROM sys.indexes a28     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_id30   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 @ = 1 THEN REPLACE(@Columns ,',',CHAR(10) + REPLICATE(CHAR(9),2) + ',') 37        WHEN @ = 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.name42             FROM sys.columns a43               WHERE object_id = OBJECT_ID(@TableName)44               ORDER BY column_id45               FOR '')),1,2,'')  46      ELSE '' END47     + CASE WHEN @ = 1 AND @HasAction = 1 THEN CHAR(10)+ REPLICATE(CHAR(9),2) + ',[Action]' 48        WHEN @ = 2 AND @HasAction = 1 THEN CHAR(10)+ REPLICATE(CHAR(9),2) + ',t.c.value([email protected]'',''tinyint'') [Action]' 49      ELSE '' END 50   + '51   FROM ' + CASE @ WHEN 1 THEN ' OPEN''' + @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 a56                   INNER JOIN sys.systypes b ON a.system_type_id = b.xtype AND b.status = 057                   WHERE object_id = OBJECT_ID(@TableName)58                    AND a.is_computed = 059                    ORDER BY column_id60                    FOR '')61                    ),1,8,'') + 62               + CASE WHEN @HasAction = 1 THEN CHAR(10)+ REPLICATE(CHAR(9),6) + ',[Action] tinyint [email protected]'')' ELSE ')' END 63               WHEN 2 THEN ' @' + @TableName + '.nodes([email protected]+''') as t(c)'64               ELSE '' END 65       + '),66 TAR AS( SELECT ' + REPLACE(@Columns,',',CHAR(10) + REPLICATE(CHAR(9),2) + ',') + '67     FROM ' + @TableName + ')68 MERGE TAR69 USING SOUR70 ON [email protected]+'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 a76       WHERE object_id = OBJECT_ID(@TableName)77        AND is_computed = 078        AND is_identity = 079        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_id81       FOR '')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

(因为偷懒,所以使用的open

然后看下生成的情况,这个是使用

 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([email protected]','tinyint') [Action] 8   FROM @employee.nodes('root/employee') as t(c)), 9 TAR AS( SELECT ID10     ,name11     ,age12     ,birthdate13     ,salary14     FROM employee)15 MERGE TAR16 USING SOUR17 ON TAR.ID = SOUR.ID18 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;

这个是使用open

;WITH SOUR AS(SELECT ID    ,name    ,age    ,birthdate    ,salary    ,[Action]  FROM OPEN@,'root/employee',3)          WITH(ID INT 'ID'            ,name NVARCHAR(100) 'name'            ,age INT 'age'            ,birthdate DATE 'birthdate'            ,salary MONEY 'salary'            ,[Action] tinyint [email protected]')),TAR AS( SELECT ID    ,name    ,age    ,birthdate    ,salary    FROM employee)MERGE TARUSING SOURON TAR.ID = SOUR.IDWHEN 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;

open

恩~然后就可放进去执行啦~~

这里只是一个很基本的用法。有几点要说明的

1、

2、On的匹配模型我是使用主键来进行对应

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