How to Transfer Your SQL Data
In addition to using the INSERT and UPDATE statements in SQL, you can add data to a table or view by using the MERGE statement. You can MERGE data from a source table or view into a destination table or view.
The MERGE can either insert new rows into the destination table or update existing rows. MERGE is a convenient way to take data that already exists somewhere in a database and copy it to a new location.
For example, consider a veterinary laboratory database. Suppose some people in the EMPLOYEE table are salespeople who have taken orders, whereas others are non-sales employees or salespeople who have not yet taken an order. The year just concluded has been profitable, and you want to share some of that success with the employees.
You decide to give a bonus of $100 to everyone who has taken at least one order and a bonus of $50 to everyone else. First, you create a BONUS table and insert into it a record for each employee who appears at least once in the ORDERS table, assigning each record a default bonus value of $100.
Next, you want to use the MERGE statement to insert new records for those employees who have not taken orders, giving them $50 bonuses. Here’s some code that builds and fills the BONUS table:
CREATE TABLE BONUS ( EmployeeName CHARACTER (30) PRIMARY KEY, Bonus NUMERIC DEFAULT 100 ) ; INSERT INTO BONUS (EmployeeName) (SELECT EmployeeName FROM EMPLOYEE, ORDERS WHERE EMPLOYEE.EmployeeName = ORDERS.Salesperson GROUP BY EMPLOYEE.EmployeeName) ;
You can now query the BONUS table to see what it holds:
SELECT * FROM BONUS ; EmployeeName Bonus ------------ ------------- Brynna Jones 100 Chris Bancroft 100 Greg Bosser 100 Kyle Weeks 100
Now, by executing a MERGE statement, you can give $50 bonuses to the rest of the employees:
MERGE INTO BONUS USING EMPLOYEE ON (BONUS.EmployeeName = EMPLOYEE.EmployeeName) WHEN NOT MATCHED THEN INSERT (BONUS.EmployeeName, BONUS.bonus) VALUES (EMPLOYEE.EmployeeName, 50) ;
Records for people in the EMPLOYEE table that don’t match records for people already in the BONUS table are now inserted into the BONUS table. Now a query of the BONUS table gives the following result:
SELECT * FROM BONUS ; EmployeeName Bonus -------------- ----------- Brynna Jones 100 Chris Bancroft 100 Greg Bosser 100 Kyle Weeks 100 Neth Doze 50 Matt Bak 50 Sam Saylor 50 Nic Foster 50
The first four records, which were created with the INSERT statement, are in alphabetical order by employee name. The rest of the records, added by the MERGE statement, appear in whatever order they were listed in the EMPLOYEE table.
The MERGE statement is a relatively new addition to SQL and may not yet be supported by some DBMS products. Even newer is an additional capability of MERGE added in SQL:2011, paradoxically enabling you to delete records with a MERGE statement.
Suppose, after doing the INSERT, you decide that you do not want to give bonuses to people who have taken at least one order after all, but you do want to give a $50 bonus to everybody else. You can remove the sales bonuses and add the non-sales bonuses with the following MERGE statement:
MERGE INTO BONUS USING EMPLOYEE ON (BONUS.EmployeeName = EMPLOYEE.EmployeeName) WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT (BONUS.EmployeeName, BONUS.bonus) VALUES (EMPLOYEE.EmployeeName, 50);
The result is
SELECT * FROM BONUS; EmployeeName Bonus -------------- ----------- Neth Doze 50 Matt Bak 50 Sam Saylor 50 Nic Foster 50