Merge

Desc

类似如下的需求:

对数据表Table插入对应的数据Data

如果Table中存在,而Data中也存在的数据则将其激活(set archived = 0)

如果Table中不存在,而Data中存在的数据则插入Table

需求可以进行扩展:

如果Table中存在,而在Data中不存在的数据则从Table中删除(set archived = 1)

Merge

如果用旧版本的SQL可能需要各种Join,Exists,Insert,Update,Delete

但是实际上用一条含有Merge的关键字就可以实现这样的需求

用法可以参考MS的API

Example

    /* merge data from this small table*/
    declare @MergeFrom as table(
        a integer
    )
    
    /* data saved in database*/
    declare @db as table(
        a integer,
        archived integer
    )

    insert into @MergeFrom values (2)
    insert into @MergeFrom values (3)
    insert into @MergeFrom values (4) /*will be inserted */

    insert into @db values(1,0) /*will be archived */
    insert into @db values(2,0)
    insert into @db values(3,1) /*will be updated*/
    

    /* Before merge */
    select * 
     from @db

    merge into @db as t
    using @MergeFrom as f
        on t.a = f.a
    when matched
        /* matched: these record are in use, set not archived */
        then update set t.archived = 0 
    when not matched 
        /* record not matched in @MergeFrom: insert into @MergeFrom */
        then insert values(f.a, 0) 
    when not matched by source
        /*record not matched in @MergeFrom:*/
        then delete; 

    /* after Merge */
    select * 
     from   @db
  • 这里对于when 之后的条件可以进行扩展, 比如添加AND 或者其他条件等等
  • Then后面可以接一个完整的SQL语句
  • Merge后面也可以添加各种select的修饰符, 比如top等等

运行结果