Using Merge in SQL Server 2012 to Insert/Update Data

Using MERGE in SQL Server 2012 to insert/update data

It's really not that hard....

You need:

• a source table (or query) to provide data
• a target table to merge it into
• a condition on which those two tables are checked
• a statement what to do if a match (on that condition) is found
• a statement what to do if NO match (on that condition) is found

So basically, it's something like:

-- this is your TARGET table - this is where the data goes into    
MERGE dbo.SomeTable AS target       
-- this is your SOURCE table where the data comes from 
USING dbo.AnotherTable AS source    
-- this is the CONDITION they have to "meet" on
ON (target.SomeColumn = source.AnotherColumn)  

-- if there's a match, so if that row already exists in the target table,
-- then just UPDATE whatever columns in the existing row you want to update
WHEN MATCHED THEN                           
    UPDATE SET Name = source.Name,
               OtherCol = source.SomeCol

-- if there's NO match, that is the row in the SOURCE does *NOT* exist in the TARGET yet,
-- then typically INSERT the new row with whichever columns you're interested in
WHEN NOT MATCHED THEN  
    INSERT (Col1, Col2, ...., ColN)  
    VALUES (source.Val1, source.Val2, ...., source.ValN);

Using SQL Merge or UPDATE / INSERT

UPDATE m SET 
  col2 = t.col2, 
  col3 = t.col3 -- etc. - all columns except Customer_Number
FROM dbo.Master_File AS m
INNER JOIN 
(
  SELECT 
    Customer_Number, rn = ROW_NUMBER() OVER
    (
      PARTITION BY Customer_Number ORDER BY [timestamp_column] DESC
    ), col2, col3, ... etc ...
  FROM dbo.Temp_Table
) AS t
ON m.Customer_Number = t.Customer_Number
WHERE t.rn = 1;

INSERT dbo.Master_File(Customer_Number, col2, col3, ...etc...)
  SELECT Customer_Number, col2, col3, ...etc...
  FROM 
  (
    SELECT 
      Customer_Number, rn = ROW_NUMBER() OVER 
      (
        PARTITION BY Customer_Number ORDER BY [timestamp_column DESC
      ),
      col2, col3, ...etc...
    FROM dbo.Temp_Table AS t 
    WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.Master_File AS m
      WHERE m.Customer_Number = t.Customer_Number
    )
  ) AS x WHERE rn = 1;

This takes care of multiple rows in the source table that don't already exist in the destination. I've made an assumption about column names which you'll have to adjust.

MERGE may be tempting, however there are a few reasons I shy away from it:

1. the syntax is daunting and hard to memorize...
2. you don't get any more concurrency than the above approach unless you intentionally add specific locking hints...
3. there are many unresolved bugs with MERGE and probably many more that have yet to be uncovered...

I recently published a cautionary tip here as well and have collected some other opinions here.

How do you merge two different SQL Server 2012 database tables in single stored procedure?

Since:

target_table cannot be a remote table. target_table cannot have any
rules defined on it.

What you could do is first insert all the data from your linked server to your current server database table using four-part query, then do Merge.

OR:

using source table as remote table because remote table is supported in USING. So what you could do alternatively is:

first Change connection to [160.80.3.220].[sample]

then:

MERGE [dbo].[Products] AS TARGET

USING [linked server instance].[database].[schema].UpdatedProducts AS SOURCE 

TSQL 2012 - MERGE - OUTPUT - change data capture - union inserted and deleted

I know I can populate an interim #temp table and then "unpivot" the data from that table in a separate step but I am looking for a one-step operation.

This is very good question. The MERGE statement is Swiss-Army Knife so I tried this approach:

INSERT INTO
SELECT ...
FROM (
     MERGE
     ...
     OUTPUT
     ...
     )

There is one problem though, I cannot split row into two because I cannot use GROUP BY, CROSS JOIN, CROSS APPLY, nested subquery, PIVOT, UNPIVOT, UNION ALL, cte. Every attempt ends with:

 A nested INSERT, UPDATE, DELETE, or MERGE statement 
 is not allowed as the table source of a PIVOT or UNPIVOT operator.

 A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed
 in a SELECT statement that is not the immediate source of rows 
 for an INSERT statement.

 A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed
 on either side of a JOIN or APPLY operator.

 The GROUP BY clause is not allowed when the FROM clause contains 
 a nested INSERT, UPDATE, DELETE, or MERGE statement.

The closest result set I get is (for UPDATE you will get only new/old values at time):

;INSERT INTO #cdc(action, execution_id, rowversion,id, somedata, someotherdata)
SELECT 
    [action]
   ,[execution_id]
   ,[rowversion]    = CASE action WHEN -1 THEN 0 
                                  WHEN 1  THEN 1 
                                  WHEN 0  THEN 1
                      END
   ,[id]            = CASE action WHEN -1 THEN id_old 
                                  WHEN 1  THEN id_new 
                                  WHEN 0  THEN id_old
                      END
   ,[somedata]      = CASE action WHEN -1 THEN somedata_old
                                  WHEN 1  THEN somedata_new
                                  WHEN 0  THEN somedata_old
                      END 
   ,[someotherdata] = CASE action WHEN -1 THEN someotherdata_old 
                                  WHEN 1  THEN someotherdata_new
                                  WHEN 0  THEN someotherdata_old
                      END
FROM (
    MERGE #t1 tgt
    USING (VALUES (1, 'aaa', 100),(2, 'bbb', 200),
                  (3, 'fff', 333), (4, 'ccc', 4444),
                  (50, 'xxx', 5050)) AS src(id, somedata, someotherdata)
    ON tgt.id = src.id
    WHEN MATCHED AND NOT EXISTS (SELECT src.somedata, src.someotherdata
                                 INTERSECT
                                 SELECT tgt.somedata, tgt.someotherdata)
    THEN
        UPDATE SET
               tgt.somedata = src.somedata
             , tgt.someotherdata = src.someotherdata
    WHEN NOT MATCHED BY TARGET THEN
        INSERT
        VALUES ( src.id
               , src.somedata
               , src.someotherdata
               )
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE
    OUTPUT
    CASE $action
      WHEN 'DELETE' THEN -1
      WHEN 'UPDATE' THEN 0
      ELSE 1 -- 'INSERT'
    END AS action
  , 1 AS execution_id
  , Inserted.*
  , Deleted.*) 
AS m(action, execution_id, id_new, somedata_new, someotherdata_new
    ,id_old, somedata_old, someotherdata_old);

LiveDemo

Also keep in mind that in current form your MERGE will UPDATE rows you marked as the same. I added additional condtion to check if at least one column is changed.

WHEN MATCHED AND NOT EXISTS (SELECT src.somedata, src.someotherdata
                             INTERSECT
                             SELECT tgt.somedata, tgt.someotherdata)
THEN

Another approach is to add triggers on target table for UPDATE/INSERT/DELETE that will insert records to cdc table and forget about OUTPUT clause in MERGE.

Using Merge with Trigger in SQL Server 2012

In Sql Server, Trigger will be called at statement level not row level. So the Inserted magic table will have all the updated/inserted records.

Alter the trigger like this

UPDATE a
SET    Column5 = CASE
                   WHEN Column5 = 'S' THEN 'Y'
                   WHEN Column5 = 'N' THEN 'N'
                   ELSE 'U'
                 END
FROM   ABC a
       INNER JOIN inserted i
               ON i.Column4 = a.Column4 

Capturing Insert ,Update and Delete counts from Merge

Just to get terminology right, @archive is a table variable, not a temp table. (they are different in many ways). A temp table looks like this #archive

To solve your problem you can simply load into a table variable as normal then insert from that table variable afterwards. This activity is in two steps. It doesn't need to all be done in one step.

Also FYI, it's not necessary to use merge at all, you can use seperate insert/update statements. A lot of people don't realise this. Also keep in mind this list of issues with merge. They are mostly edge cases but keep in mind that there are other options

https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

      -- Create the table variable to capture the output
      DECLARE @MergeOutput (
            Action_Out VARCHAR(100),
            [WarehouseID] INT,
            [WarehouseCode] VARCHAR(50),
            [WarehouseName] VARCHAR(50)
      );

        -- mere into table, capturing output into table variable
        MERGE [DWDB].[dbo].[Dim_Warehouse] AS stg
        USING SourceDB.dbo.Warehouse AS SRC    
        ON (stg.WarehouseID = SRC.WarehouseID) 
        WHEN NOT MATCHED     
        THEN INSERT VALUES
            (
             SRC.[WarehouseID]
            ,SRC.[WarehouseCode]
            ,SRC.[WarehouseName]
            )
    WHEN MATCHED
        AND stg.dw_EndDate IS NULL
        AND 
            (
              stg.[WarehouseName]   <> src.[WarehouseName]
            ) 

        THEN
            UPDATE 
            SET  stg.dw_EndDate =  GETDATE()
                ,stg.dw_IsCurrent = 0 
    OUTPUT $action Action_Out
            ,SRC.[WarehouseID]
            ,SRC.[WarehouseCode]
            ,SRC.[WarehouseName]
    INTO @MergeOutput

-- Perform insert based on table variable
INSERT INTO  [DWDB].[dbo].[Dim_Warehouse]      
    (
         [WarehouseID]
        ,[WarehouseCode]
        ,[WarehouseName]
    )   
SELECT 
         [WarehouseID]
        ,[WarehouseCode]
        ,[WarehouseName]
FROM @MergeOutput
WHERE Action_Out = 'UPDATE';

SQL Server MERGE, if exist then update else insert

You don't add any rows because your using clause is not returning any rows. Put your constants in the using clause with column aliases and use the fields in the when not matched

Something like this with a couple of fields removed simplicity.

merge bc_data2 as T
using (select '1.1.1.2' as ip,
              '2012-02-29' as report_date,
              1194370142 as value) as S
on T.ip = S.ip and
   T.report_date = S.report_date
when matched then
  update set T.value = T.value + 1
when not matched then
  insert (ip, report_date, value) 
    values(ip, report_date, value);

Update field in Source table with Identity after insert target with merge statement

You can do this with a merge..output to a table variable followed by an update.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table Items
(
  ItemsId int identity primary key,
  ItemName nvarchar(50) not null,
  ItemsLanguageTextId int null
);

create table ItemsLanguageText
(
  ItemsLanguageTextId int identity primary key,
  Text nvarchar(50) not null
);

insert into Items values('Name 1', null);
insert into Items values('Name 2', null);
insert into Items values('Name 3', null);

Query 1:

declare @T table
(
  ItemsId int,
  ItemsLanguageTextId int
);

merge ItemsLanguageText as T
using (
        select ItemsId, ItemName
        from Items
        where ItemsLanguageTextId is null
      ) as S
on 0 = 1
when not matched then
  insert (Text) values (S.ItemName)
output S.ItemsId, inserted.ItemsLanguageTextId
  into @T;

update Items
set ItemsLanguageTextId = T.ItemsLanguageTextId
from @T as T
where T.ItemsId = Items.ItemsId;

Results:

Query 2:

select * from Items;

Results:

| ITEMSID | ITEMNAME | ITEMSLANGUAGETEXTID |
--------------------------------------------
|       1 |   Name 1 |                  13 |
|       2 |   Name 2 |                  14 |
|       3 |   Name 3 |                  15 |

Query 3:

select * from ItemsLanguageText;

Results:

| ITEMSLANGUAGETEXTID |   TEXT |
--------------------------------
|                  13 | Name 1 |
|                  14 | Name 2 |
|                  15 | Name 3 |

SQL Server - Merge Insert/Update from Select query

MERGE BMS_Snapshot target USING (SELECT BMS_Snapshot.BuildingID, ...) 
 source(BuildingID,...)
  ON target.BuildingID = source.BuildingID 
  AND target.Timestamp = source.Timestamp 
WHEN MATCHED THEN 
    UPDATE SET Emissions = source.Emissions, ... 
WHEN NOT MATCHED BY target THEN 
    INSERT (BuildingID, ...) 
    VALUES (source.BuildingID, ...);

I apologize for the format, as I'm on a tablet at the moment.

per your update, you are only looking at buildingid in the ON clause, but you also need your timestamp. The buildingid matches but the filter on the WHEN MATCHED eliminates it from the update.

---

Related Topics