Merge, multi – table insert and pivot insert | Oracle DBA Tutorial pdf

Merge, multi – table insert and pivot insert

An example of merge command is shown below.
MERGE INTO MANUFACTURER M
USING NEW_MANUFACTURER WM
ON (M.MFDNO = WM.MFDNO)
WHEN MATCHED THEN UPDATE
SET M.MFDNAME = WM.MFDNAME, M.ADDRESS = WM.ADDRESS, M.CITY = WM.CITY,
M.STATE = WM.STATE, M.COUNTRY = WM.COUNTRY
WHEN NOT MATCHED THEN
INSERT (M.MFDNO, M.MFDNAME, M.ADDRESS, M.CITY, M.STATE, M.COUNTRY)
VALUES (WM.MFDNO, WM.MFDNAME, WM.ADDRESS, WM.CITY, WM.STATE, M.COUNTRY);
Similarly, an example of multi – table insert follows.
INSERT ALL
WHEN MEDIA = ‘BOOK’ THEN
INTO BOOK VALUES (NO, TITLE, PRICE)
WHEN MEDIA = ‘CD’ THEN
INTO SOFTWARE VALUES (NO, TITLE, PRICE)
WHEN MEDIA = ‘VCD’ THEN
INTO VIDEO VALUES (NO, TITLE, PRICE)
SELECT MEDIA, NO, TITLE, MEDIA FROM PRODUCT;
Using pivot insert, you can create multiple rows of data from single record. Say, SALES_SOURCE_DATA comes from a non-relational source and it contains following columns – emp_id, sales_mon, sales_tue, sales_wed, sales_thu and sales_fri. We like to store this information in SALES_INFO table which has
following fields – emp_id, week, sales. We can achieve it by using the statement shown below.
INSERT INTO SALES_INFO VALUES (emp_id, week, sales_mon)
INSERT INTO SALES_INFO VALUES (emp_id, week, sales_tue)
INSERT INTO SALES_INFO VALUES (emp_id, week, sales_wed)
INSERT INTO SALES_INFO VALUES (emp_id, week, sales_thu)
INSERT INTO SALES_INFO VALUES (emp_id, week, sales_fri)
SELECT emp_id, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri FROM
SALES_SOURCE_DATA.
Thus for 1 row in SALES_SOURCE_DATA, we shall have 5 rows in SALES_INFO table. This feature is also known as normalization is some other database applications.