Old Dog, New Phone – Insert into multiple tables in a single statement

I got a new ( and possibly new-fangled) phone recently.
Having spent a suitably geeky amount of time exploring the new features of this slab of Android lovliness I did come across something which left me quite stumped… somebody rang me up on it.
Increasingly frantic tapping of the little green phone icon failed to result in me answering the call.
It was at this point that Deb took pity and explained in that ever-so-patient way people use to the hard of thinking, that I simply needed to swipe.
Needless to say, she’s not ever mentioned the incident again and would never dream of taking any opportunity to bring it up to elicit a laugh at my expense. Ahem.
It’s unfortunate that I also got a work phone recently and spent five minutes tapping the screen before I worked out that the Blackberry I was holding was not, in fact, a touch-screen device.
As the fast-paced world of consumer electronics passes by in an ever-increasing blur, I’m comforted by the fact that I can still learn something new.
For example, I was recently faced with a situation requiring me to load data from one table into two further tables, depending on certain criteria.
With the aid of my trusty netbook ( a form-factor that is apparently as outdated as it’s owner), I shall now demonstrate the wonder of the age that is the multi-table insert statement.

At this point, I should acknowledge this excellent post by Adrian Billington, which covers multi-table inserts in a bit more depth than I do here.

The challenge

Say, we have a table called SMART_PHONES that looks something like this :

CREATE TABLE smart_phones(
    manufacturer VARCHAR2(30),
    model VARCHAR2(30),
    operating_system VARCHAR2(30))
/

INSERT INTO smart_phones(
    manufacturer, model, operating_system)
VALUES(
    'SAMSUNG', 'GALAXY', 'ANDROID')
/

INSERT INTO smart_phones(
    manufacturer, model, operating_system)
VALUES(
    'APPLE', 'IPHONE', 'IOS')
/

INSERT INTO smart_phones(
    manufacturer, model, operating_system)
VALUES(
    'GOOGLE', 'NEXUS', 'ANDROID')
/

INSERT INTO smart_phones(
    manufacturer, model, operating_system)
VALUES(
    'SONY', 'XPERIA', 'ANDROID')
/

INSERT INTO smart_phones(
    manufacturer, model, operating_system)
VALUES(
    'RIM', '9230', 'BLACKBERRY')
/

COMMIT;

I did briefly consider adding a comments column to this table so that I could make some snarky comments about Corporation Tax…but then I realised that they would probably apply to all of the records in the table.

The records in this table need to be further classified on the basis of whether or not they are running on Android by means of allocating them to the appropriate table of these two :

CREATE TABLE android_phones(
    manufacturer VARCHAR2(30),
    model VARCHAR2(30))
/

CREATE TABLE non_android_phones(
    manufacturer VARCHAR2(30),
    model VARCHAR2(30),
    operating_system VARCHAR2(30))
/

The traditional approach

Inserting into two tables, well, you need two insert statements, right ?

INSERT INTO android_phones
SELECT manufacturer, model
FROM smart_phones
WHERE operating_system = 'ANDROID'
/

INSERT INTO non_android_phones
SELECT manufacturer, model, operating_system
FROM smart_phones
WHERE operating_system != 'ANDROID'
/

A bit less typing

Step forward, the all-singing, all-dancing, insert where you want statement.

INSERT FIRST
    WHEN operating_system = 'ANDROID' THEN
        INTO android_phones( manufacturer, model)
        VALUES( manufacturer, model)
    ELSE
        INTO non_android_phones
SELECT manufacturer, model, operating_system
FROM smart_phones
/

The syntax probably merits some explanation :

INSERT FIRST – this means we’ll insert each row only once – i.e. it will be inserted into the table associated with the first condition to be satisfied.
you can also use the keyword ALL here, but that would mean that your record is inserted into every table specified.
I’ve not included the column specification in the ELSE insert as the columns in the select statement exactly match the columns in the target table. I’ve really done this to demonstrate the fact that you can. Whether you should is probably another matter.

Anyway, let’s run it and check we get the results we expect…

SQL> SELECT * FROM smart_phones;

MANUFACTURER	     MODEL		  OPERATING_SYSTEM
-------------------- -------------------- --------------------
SAMSUNG 	     GALAXY		  ANDROID
APPLE		     IPHONE		  IOS
GOOGLE		     NEXUS		  ANDROID
SONY		     XPERIA		  ANDROID
RIM		     9230		  BLACKBERRY

SQL> INSERT FIRST
  2      WHEN operating_system = 'ANDROID' THEN
  3          INTO android_phones(manufacturer, model)
  4          VALUES( manufacturer, model)
  5      ELSE
  6          INTO non_android_phones
  7  SELECT manufacturer, model, operating_system
  8  FROM smart_phones
  9  /

5 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM android_phones;

MANUFACTURER	     MODEL
-------------------- --------------------
SAMSUNG 	     GALAXY
GOOGLE		     NEXUS
SONY		     XPERIA

SQL> SELECT * FROM non_android_phones;

MANUFACTURER	     MODEL		  OPERATING_SYSTEM
-------------------- -------------------- --------------------
APPLE		     IPHONE		  IOS
RIM		     9230		  BLACKBERRY

SQL> 

As well as being less typing, multi-insert seems to perform about the same (if not a little quicker) than the traditional approach.
All of which gives me a bit more time to consider the suitably old axiom…”Beware of any gadget that weighs less than it’s instruction manual”.

About these ads

2 thoughts on “Old Dog, New Phone – Insert into multiple tables in a single statement

  1. Just to add…if you leave out the keyword FIRST and the keyword ALL, each row will get processed through each condition…

    create table apple_devices (device_name varchar2(100));
    create table phone_devices (device_name varchar2(100));
    create table junk_devices (device_name varchar2(100));
    create table device_source (device_type varchar2(100), device_name varchar2(100));

    insert into device_source VALUES (‘PHONE’,’APPLE IPHONE’);
    insert into device_source VALUES (‘PHONE’,’GOOGLE ANDROID’);
    insert into device_source VALUES (‘COMPUTER’,’APPLE IMAC’);
    insert into device_source VALUES (‘PHONE’,’WINDOWS PHONE’);
    insert into device_source VALUES (‘JUNK’,’BLACKBERRY’);

    INSERT
    WHEN device_type = ‘PHONE’ THEN
    INTO phone_devices(device_name)
    VALUES( DEVICE_NAME)
    WHEN DEVICE_NAME LIKE ‘APPLE%’ THEN
    INTO apple_devices(device_name)
    VALUES( DEVICE_NAME)
    ELSE
    INTO junk_devices (DEVICE_NAME) VALUES (DEVICE_NAME)
    SELECT DEVICE_TYPE, DEVICE_NAME
    FROM device_source;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s