How to use MERGE and then to split one source record to more target records?

Question

I am writing a data pump that gets information from the source_table with columns code, some_data, oz1, oz2, oz3, oz4, and I do store it in target_table with the same structure. In parallel, I would like to update also another table with different structure (say oz_table) -- records code, oz -- that is four records instead of one record with four values (at maximum because the empty and NULL values for ozX are not to be stored).

I am using the MERGE command (Microsoft T-SQL) for the existing target_table (four oz's in one record -- the old approach). The INSERTed/UPDATEd records are collected using the OUTPUT mechanism into a table variable @info_table. (The target records are intentionally never deleted if the source records disappear; so, there is no DELETE action.)

So far, I have a code like this:

CREATE PROCEDURE dbo.data_pump
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @result int = -555   -- init (number of affected records)

    DECLARE @info_table TABLE (
        action nvarchar(10),
        code int,
        oz1 nvarchar(40),
        oz2 nvarchar(40),
        oz3 nvarchar(40),
        oz4 nvarchar(40)
    )

    BEGIN TRANSACTION tran_data_pump
    BEGIN TRY
        MERGE target_table AS target
        USING (SELECT code, some_data, oz1, oz2, oz3, oz4
               FROM source_table) AS source
        ON target.code = source.code
        WHEN MATCHED AND (COALESCE(target.some_data, '') != COALESCE(source.some_data, '')
                          OR COALESCE(target.oz1, '') != COALESCE(source.oz1, '')
                          OR COALESCE(target.oz2, '') != COALESCE(source.oz2, '')
                          OR COALESCE(target.oz3, '') != COALESCE(source.oz3, '')
                          OR COALESCE(target.oz4, '') != COALESCE(source.oz4, '')
                          ) THEN
            UPDATE
            SET target.some_data = source.some_data,
                target.oz1 = source.oz1,
                target.oz2 = source.oz2,
                target.oz3 = source.oz3,
                target.oz4 = source.oz4
        WHEN NOT MATCHED THEN
            INSERT (code, some_data,
                    oz1, oz2, oz3, oz4)
            VALUES (source.code, source.some_data,
                    source.oz1, source.oz2, source.oz3, source.oz4)
        OUTPUT
            $action AS action,  -- INSERT or UPDATE
            inserted.code AS code,
            inserted.oz1 AS oz1,
            inserted.oz2 AS oz2,
            inserted.oz3 AS oz3,
            inserted.oz4 AS oz4
        INTO @info_table;

        SET @result = @@ROWCOUNT

        COMMIT TRANSACTION tran_data_pump
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION tran_data_pump
        SET @result = -1    -- transaction-failed indication
    END CATCH
    RETURN @result  -- OK, number of the transfered records
END

It works fine, so far. Now I would like to process the @info_table to insert/update the oz_table. For the action UPDATE, the records with the code should be deleted first, and the new ones should be inserted. The order is not important, and the new number of the inserted records may be different. The NULL or empty strings in oz's should not produce any record. For the INSERT action, the situation is simpler just to insert the new records.

Update: The question was slightly modified to clarify the core of the question. The data tables can be defined like that:

CREATE TABLE dbo.source_table (
    ID int IDENTITY PRIMARY KEY NOT NULL,
    code int,
    some_data nvarchar(50),
    oz1 nvarchar(40),
    oz2 nvarchar(40),
    oz3 nvarchar(40),
    oz4 nvarchar(40)
)

CREATE TABLE dbo.target_table (
    ID int IDENTITY PRIMARY KEY NOT NULL,
    code int,
    some_data nvarchar(50),
    oz2 nvarchar(40),
    oz3 nvarchar(40),
    oz1 nvarchar(40),
    oz4 nvarchar(40)
)

CREATE TABLE dbo.oz_table (
    ID int IDENTITY PRIMARY KEY NOT NULL,
    code int,
    oz nvarchar(40) NOT NULL
)

See the full testing script (creates database, the tables, calls the data_pump at http://pastebin.com/wBz3Tzwn

How to do it nicely? I need the the efficient solution as the amount of date may be big, and the operation should be as fast as possible.

Answer 1

If I had understood your problem statement correctly then below approach could be one way to solve -

    -- declare the temp tables
    DECLARE @info_table TABLE (
        action nvarchar(10),
        ID int,
        oz1 nvarchar(40),
        oz2 nvarchar(40),
        oz3 nvarchar(40),
        oz4 nvarchar(40)
    )
    --create intermediate table to store the results
    CREATE TABLE #temp_alternative_table (ID int,oz nvarchar(40))
    -- insert some dummy values
    INSERT INTO @info_table (action,ID,oz1,oz2,oz3,oz4)
    VALUES 
        ('INSERT',1, '85', '94', '78', '90'),
        ('UPDATE',2, '75', '88', '91', '78')
    --SELECT * FROM @info_table
    -- doing unpivot and transforming one row many columns to many rows one column and inserting into intermediate temp table
    INSERT INTO #temp_alternative_table
    SELECT *
    FROM (
        SELECT 
               Action   
            ,  ID
            , [Oz]
        FROM @info_table
        UNPIVOT 
        (
            [Oz] FOR tt IN (oz1, oz2, oz3, oz4)
        ) unpvt
    ) t
    -- delete from main table all the records for which the action is UPDATE (stored in intermediate temp table for the same ID as of main table)
    DELETE at
    FROM alternative_table at
    INNER JOIN #temp_alternative_table tat
      ON at.ID = tat.ID
    WHERE tat.action = 'UPDATE'
    -- now insert all the records in main table
    INSERT INTO alternative_table (ID,Oz)
    SELECT ID,Oz
    FROM #temp_alternative_table

Let me know if this what you were looking for. Hope this helps.

Answer 2

Since you are talking about efficiency, at first there should be appropriate indexes.

source_table and target_table should have unique index on code. It should be unique, otherwise the main MERGE would fail when it attempts to update the same row more than once.

oz_table should have non-unique index on code.

@info_table should have code as a primary key. It is not possible to have two different actions for the same code from one MERGE, so code should be unique:

DECLARE @info_table TABLE 
(
    action nvarchar(10),
    code int PRIMARY KEY,
    oz1 nvarchar(40),
    oz2 nvarchar(40),
    oz3 nvarchar(40),
    oz4 nvarchar(40)
);

There is no need for extra temporary tables. We already have one - @info_table.

After the MERGE, when @info_table is populated we need to do two steps: 1) delete some rows from oz_table, 2) add some rows to oz_table.

At first delete from oz_table those rows that were updated by the MERGE. In fact, those rows that were inserted by the MERGE would not exist in oz_table any way, so we can use a simple DELETE statement. There is no need to explitly filter by UPDATE action. Such filter would not remove any rows.

If there is a chance that oz_table can be changed outside this data pump process, then extra filter is needed.

DELETE FROM dbo.oz_table
WHERE dbo.oz_table.code IN
    (
        SELECT code FROM @info_table
    )
;

Indexes on code on both tables would help to join them effiently.

Then, simply insert both updated and inserted rows.

INSERT INTO dbo.oz_table(code, oz)
SELECT
    T.code
    ,CA.oz
FROM
    @info_table AS T
    CROSS APPLY
    (
        VALUES
            (T.oz1),
            (T.oz2),
            (T.oz3),
            (T.oz4)
    ) AS CA (oz)
WHERE
    CA.oz IS NOT NULL
    AND CA.oz <> ''
    -- The NULL or empty strings should not produce any record
;   

I prefer to use CROSS APPLY and VALUES instead of UNPIVOT. We want to remove NULL and empty values, so I think it is better to write the filter explicitly, so that everybody reading the code sees it. With UNPIVOT you need to know that it removes NULL implicitly. You'll still have to add a filter to remove empty values. In this case the performance would mosts likely be the same, but you'd better check on the real data.

---

If there is a chance that this data pump procedure can be called more than once at the same time, then you have to take extra steps to prevent possible concurrency problems (incorrect results or deadlocks). I prefer to use sp_getapplock to guarantee that only one instance of a stored procedure can run at any moment.

Answer 3

A slightly different approach to consider is to define an update/insert trigger on your target_table. With this method your data pump only need to consider your initial target table. The trigger will transform and insert the merged data into the alternative_table table.

If you are willing to incur the extra data storage cost of storing the original oz column names as a search key in your alternative table, then you can use another merge statement which will improve the overall performance of the trigger as follow:

-- Create example table
CREATE TABLE [dbo].[alternative_table](
    [ID] [int] NOT NULL,
    [ColumnKey] [nvarchar](5) NOT NULL,
    [oz] [nvarchar](100) NULL,
 CONSTRAINT [PK_alternative_table] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [ColumnKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Create trigger responsible for populating Alternative table.
CREATE TRIGGER dbo.MergeWithAlternative
   ON  dbo.target_table
   AFTER INSERT, UPDATE
AS 
BEGIN

    SET NOCOUNT ON;


    MERGE [alternative_table] AS [target]
    USING   (
                SELECT 
                      [ID]
                    , [ColumnKey]
                    , [Oz]
                FROM inserted
                UNPIVOT 
                (
                    [Oz] FOR [ColumnKey] IN (oz1, oz2, oz3, oz4)
                ) unpvt
            ) AS [source]
    ON [target].ID = [source].ID AND [target].[ColumnKey] = [source].[ColumnKey]
    WHEN MATCHED THEN
        UPDATE
        SET [target].oz = [source].[Oz]
    WHEN NOT MATCHED THEN
        INSERT 
        (
            ID
            ,[ColumnKey] 
            ,[oz]
        )
        VALUES 
        (
            source.ID
            ,source.[ColumnKey]
            ,source.[Oz]
        );

END

If you are not willing to store the column name as a key lookup then a simple delete / insert action will work as well:

-- Create example table
CREATE TABLE [dbo].[alternative_table](
    [ID] [int] NOT NULL,
    [oz] [nvarchar](100) NULL
) ON [PRIMARY]

GO

-- Create trigger responsible for populating Alternative table.
CREATE TRIGGER dbo.MergeWithAlternative
   ON  dbo.target_table
   AFTER INSERT, UPDATE
AS 
BEGIN

    SET NOCOUNT ON;


    DELETE [dbo].[alternative_table]
    WHERE   [ID] IN (SELECT ID FROM deleted)

    INSERT INTO [dbo].[alternative_table]
    (
        [ID]
        ,[oz]
    )
    SELECT  [ID]
            ,[Oz]
    FROM inserted
    UNPIVOT 
    (
        [Oz] FOR [ColumnKey] IN (oz1, oz2, oz3, oz4)
    ) unpvt

END

The table variable and the output clause that populated it should not be needed anymore with this approach.

Answer 4

Since we need to build the oz_table by replacing the old records and adding new records, I prefer to truncate it first, and rebuild it from scratch by INSERTING all the records.. I'll use two cte's, the first one to read the @info_table while the second one to construct all the rows by UNION all the four oz columns. Then just insert the UNION to the oz_Table.

Truncate table dbo.oz_table

with cte as(
    Select Code, oz1, oz2, oz3, oz4 from @info_table
), cte2 as(
            Select Code, oz1 as oz From cte
  UNION ALL Select Code, oz2 as oz From cte
  UNION ALL Select Code, oz3 as oz From cte
  UNION ALL Select Code, oz4 as oz From cte
)
Insert into dbo.oz_table(Code, oz)
select Code, oz from cte2
Where oz is not null and oz<>''