insert_product_nodes_data.sql

-- Root (Top) Node (has no parent node)
INSERT INTO product_nodes (node_natural_key, node_name, level_name, parent_node_id)
VALUES (0, 'All Products', 'Total Products', NULL);

-- Produce Category Level Node
INSERT INTO product_nodes (node_natural_key, node_name, level_name, parent_node_id)
VALUES (10, 'Produce', 'Category', (SELECT node_id
                                      FROM product_nodes
                                     WHERE node_name = 'All Products'));

-- Produce Category Children Leaf-Level Nodes
INSERT INTO product_nodes (node_natural_key, node_name, level_name, parent_node_id)
VALUES (101, 'Spinach', 'UPC', (SELECT node_id
                                 FROM product_nodes
                                WHERE node_name = 'Produce'));

INSERT INTO product_nodes (node_natural_key, node_name, level_name, parent_node_id)
VALUES (102, 'Tomatoes', 'UPC', (SELECT node_id
                                   FROM product_nodes
                                  WHERE node_name = 'Produce'));

-- Candy Category Level Node
INSERT INTO product_nodes (node_natural_key, node_name, level_name, parent_node_id)
VALUES (20, 'Candy', 'Category', (SELECT node_id
                                   FROM product_nodes
                                  WHERE node_name = 'All Products'));

-- Candy Category Children Leaf-Level Nodes
INSERT INTO product_nodes (node_natural_key, node_name, level_name, parent_node_id)
VALUES (201, 'Hershey Bar', 'UPC', (SELECT node_id
                                    FROM product_nodes
                                   WHERE node_name = 'Candy'));

INSERT INTO product_nodes (node_natural_key, node_name, level_name, parent_node_id)
VALUES (202, 'Nerds', 'UPC', (SELECT node_id
                              FROM product_nodes
                             WHERE node_name = 'Candy'));