MSSQL Merge 3 hierarchy tables

All we need is an easy explanation of the problem, so here it is.

I want to merge 3 hierarchy tables into one table.

The 3 tables:

CREATE TABLE dbo.tbl1 (
    key1 varchar(50) NULL,
    key2 varchar(50) NULL
) ON PRIMARY

CREATE TABLE dbo.tbl2 (
    key2 varchar(50) NULL,
    key3 varchar(50) NULL
) ON PRIMARY

CREATE TABLE dbo.tbl3 (
    key3 varchar(50) NULL,
    key4 varchar(50) NULL
) ON PRIMARY

Some sample data:

INSERT INTO tbl1 (key1, key2)
VALUES ('001','300'), ('001','301'),('005','405'),('006','406');

INSERT INTO tbl2 (key2, key3)
VALUES ('300','5554'), ('300','5555'),('405','5557'),('406','5563');

INSERT INTO tbl3 (key3, key4)
VALUES ('5554','9000'), ('5555','9005'),('5555','9006'),('5563','9007');

Visual representation of the tables with data:

        table 1                  table 2                  table 3
    +------+------+          +------+------+          +------+------+
    | key1 | key2 |          | key2 | key3 |          | key3 | key4 |
    +------+------+          +------+------+          +------+------+
    | 001  | 300  |          | 300  | 5554 |          | 5554 | 9000 |
    | 001  | 301  |          | 300  | 5555 |          | 5555 | 9005 |
    | 005  | 405  |          | 405  | 5557 |          | 5555 | 9006 |
    | 006  | 406  |          | 406  | 5563 |          | 5563 | 9007 |
    +------+------+          +------+------+          +------+------+

Can someone kindly help me join the above tables to achieve the following structure, please:

CREATE TABLE dbo.tbl4 (
    key1 varchar(50) NULL,
    key2 varchar(50) NULL,
    key3 varchar(50) NULL,
    key4 varchar(50) NULL
) ON PRIMARY

My query so far:

MERGE tbl4 t USING tbl1 s
    ON (s.key1 = t.key1)
    WHEN MATCHED THEN UPDATE SET
        t.key2 = s.key2
    WHEN NOT MATCHED BY TARGET
        THEN    INSERT(key1, key2)
                VALUES(s.key1, s.key2)
    WHEN NOT MATCHED BY SOURCE
        THEN DELETE;

The above script only works for the first table. If I try to adapt the code above to also merge tables 2 and 3 into the final one, I get the following output:

Msg 8672, Level 16, State 1, Procedure dbo.spProcessPartCodes, Line 260 [Batch Start Line 2] The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

The output I’d like to get:

           table 4 (combined)
    +------+------+-------+-------+
    | key1 | key2 | key3  | key4  |
    +------+------+-------+-------+
    | 001  | 300  | 5554  | 9000  |
    | 001  | 300  | 5555  | 9005  |
    | 001  | 300  | 5555  | 9006  |
    | 001  | 301  | null  | null  |
    | 005  | 405  | 5557  | null  |
    | 006  | 406  | 5563  | 9007  |
    +------+------+-------+-------+

I have come across unions and recursive cte’s, but I am having no luck implementing them so far.

Any help would be greatly appreciated.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Because you have four tables establishing the hierarchy you can simply join all of the elements directly:

SELECT
  level1.key1
 ,level1.key2
 ,level2.key3
 ,level3.key4
FROM
  table1 level1
LEFT JOIN
  table2 level2
    ON level2.key2 = level1.key2
LEFT JOIN
  table3 level3
    ON level3.key3 = level2.key3

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply