BogdanM BogdanM - 6 months ago 17
SQL Question

Merge syntax Sql Server 2012 error

Have one question for Merge syntax for which I cannot find the answer.

I have the following case:

Step1:

create temp table #TempTbl


Step2: Merge Sytax:

Merge into T1 target
using T2 as source
on bunch of columns
When MATCHED
UPDATE SET some columns from target equal some columns from source
When NOT MATCHED by TARGET
THEN INSERT (bunch of columns) VALUES (bunch of columns from SOURCE)
OUTPUT $action, deleted.* into #TempTbl


What I need to know is for my above steps wouldn't I find only empty data in my temporary table
#TempTbl
, as I only stated
WHEN NOT MATCHED ... THEN INSERT
, not
DELETE
?

Second question, what type of column should
$action
be, as I'm having the error message:


Column name or supplied values do not match table definition


Although I've tried to define the first column from my table both
varchar(100)
,
nvarchar(100)
, but with no luck. But, If i omit the
$action
field, then my statement works.

jpw jpw
Answer

So, the column that will hold the $action should be nvarchar(10).

The following statement would add rows to the temp table for both insert and update (as the update is really a delete followed by an insert) but with different actions:

-- sample test data
create table t1 (col1 int, col2 int)
create table t2 (col1 int, col2 int)
insert t1 values (1,1),(2,1)
insert t2 values (2,2),(3,3)
create table #temptbl (dml_action nvarchar(10), col1 int, col2 int)

-- merge statement
merge into t1 target 
using t2 as source 
  on target.col1 = source.col1
when matched 
    then update set target.col2 = source.col2
when not matched by target 
    then insert (col1, col2) values (source.col2, source.col2) 
output $action, inserted.col1, inserted.col2 into #temptbl ;

-- sample result

select * from #temptbl

dml_action col1        col2
---------- ----------- -----------
INSERT     3           3
UPDATE     2           2

If you don't want the update rows you could wrap the entire batch into another statement like so:

insert #temptbl (dml_action, col1, col2)
select dml_action, col1, col2 
from 
(
    merge into t1 target 
    using t2 as source 
     on target.col1 = source.col1
    when matched 
       then update set target.col2 = source.col2
    when not matched by target 
       then insert (col1, col2) values (source.col2, source.col2) 
    output $action as dml_action, inserted.col1, inserted.col2 
) a
where a.dml_action = 'INSERT'
Comments