Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

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
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.