sql - 如何使用 MERGE 然后将一条源记录拆分为多条目标记录？

Question

我正在编写一个数据泵，它从 source_table 的列code、some_data、oz1、oz2、oz3、oz4，我将其存储在 target_table 中相同的结构。同时，我想更新另一个具有不同结构的表(比如oz_table)——记录code，oz -- 即 4 条记录，而不是一条具有四个值的记录(最大值，因为不会存储 ozX 的空值和 NULL 值)。

我正在对现有的 target_table 使用 MERGE 命令 (Microsoft T-SQL)(一条记录中有 4 盎司——旧方法)。使用 OUTPUT 机制将 INSERTed/UPDATEd 记录收集到表变量 @info_table 中。 (如果源记录消失，目标记录将不会被删除；因此，没有 DELETE 操作。)

到目前为止，我有这样的代码:

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

到目前为止，一切正常。现在我想处理 @info_table 以插入/更新 oz_table。对于操作UPDATE，应该先删除带有code的记录，然后插入新的记录。顺序并不重要，插入记录的新数量可能不同。 oz 中的 NULL 或空字符串不应产生任何记录。对于 INSERT 操作，情况更简单，只需插入新记录。

更新:对问题稍作修改以阐明问题的核心。数据表可以这样定义:

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
)

查看完整的测试脚本(创建数据库、表、在 http://pastebin.com/wBz3Tzwn 调用 data_pump

如何做好？我需要高效的解决方案，因为数据量可能很大，并且操作应该尽可能快。

Answer 1

如果我正确理解了您的问题陈述，那么下面的方法可能是解决问题的一种方法 -

    -- 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

如果这就是您要找的，请告诉我。希望这会有所帮助。