Data Manipulation Language (INSERT Part 2)

This article entitled Data Manipulation Language (Part 2) will continue the discussions from previous article entitled Insert Data Into Table SQL. From that article, has been described and given an example of using basic Insert DML to input data into database tables. In this article we will continue the discussion of 4 other Insert DML groups: INSERT with SELECT, INSERT with WHEN, INSERT ALL and INSERT with Date Function. Keep in mind that DML Insert must end with a COMMIT command to make data permanently stored in the database table.

Previously let's look at the format and examples of basic DML INSERT commands.

Format :

INSERT INTO <table_name> (<attribute(s)>)

VALUES (<column_value(s)>);

Example :

INSERT INTO tstudent (sid, name, phone) VALUES (101, ’One’, 111111);

Okay let's continue, here is an explanation and other examples of DML Insert, the table used is TSTUDENT table

SID	NAME	PHONE
101	One	111111
102	Two	222222
103	Three	333333

2.       INSERT with SELECT

In the INSERT with SELECT command, the attribute values to be input into the destination table are derived from queries from other tables. Suppose we have another table named TTEMP with SID and NAME attributes.

Format :

INSERT INTO <table_name> <SELECT Statement>;

Example :

INSERT INTO ttemp (sid, name) SELECT sid, name FROM tstudent;

Explanations:

INSERT INTO ttemp -> Perform the insert command to the TTEMP table.

(sid, name) -> Attribute in the TTEMP table to be filled.

SELECT sid, name FROM tstudent -> Perform the selection of attributes from the TSTUDENT table.

; (colon) -> Denotes the end of a SQL command.

3.       INSERT dengan WHEN

INSERT commands coupled with WHEN statements can generate data input processes on multiple tables with certain criteria simultaneously. The INSERT process with WHEN can be used to split a table into smaller tables. For example the student detail table is split into several tables based on the original name of the existing student.

Format :

INSERT
WHEN (<condition>) THEN
  INTO <table_name> (<table_column(s)>)
  VALUES (<insert_value(s)>)
WHEN (<condition>) THEN
  INTO <table_name> (<table_column(s)>)
  VALUES (<insert_value(s)>)
ELSE
  INTO <table_name> (<table_column(s)>)
  VALUES (<insert_value(s)>)
SELECT <table_column(s)> FROM <table_name>;

Example :

CREATE TABLE tstudent_a AS SELECT * FROM tstudent WHERE 1=0;

CREATE TABLE tstudent _b AS SELECT * FROM tstudent WHERE 1=0;

CREATE TABLE tstudent _c AS SELECT * FROM tstudent WHERE 1=0;

INSERT
WHEN (SUBSTR(name,1,1)=’A’) THEN
  INTO tstudent _a (sid, name, phone)
  VALUES sid, name, phone)
WHEN (SUBSTR(name,1,1)=’B’) THEN
  INTO tstudent _b (sid, name, phone)
  VALUES (sid, name, phone)
ELSE
 INTO tstudent _c (sid, name, phone)
  VALUES (sid, name, phone)
SELECT sid, name, phone FROM tstudent;

Explanations:

CREATE TABLE tstudent_a AS SELECT * FROM tstudent WHERE 1=0; -> Create empty tables for separation targets (so do tstudent_b and tstudent_c tables).

WHEN (SUBSTR(name,1,1)=’A’)  -> Choose the contents of the name attribute that prefixes the letter 'A'.

INTO tstudent_a (sid, name, phone) -> Attribute on student_a table to be filled.

VALUES (sid, name, phone) -> Specify the attributes to be extracted from the source table (student).

SELECT sid, name, phone FROM tstudent; -> Command to specify the source of the data.

; (colon) -> Denotes the end of a SQL command.

4.       INSERT ALL

The INSERT ALL command is used to add more than one row of data simultaneously to the table using one INSERT command.

Format :

INSERT ALL
INTO <table_name> VALUES <table_column(s))
INTO <table_name> VALUES <column_name_list)
...
<SELECT Statement>;

Example :

INSERT ALL

INTO tstudent (sid, name, phone)

VALUES (104,’Andi Subagja’,’08156222345’)

INTO tstudent (sid, name, phone)

VALUES (105,’Angelina Marini’,’08164453647’)

SELECT * FROM dual;

Explanations :

INSERT ALL -> Initial command to insert more than one line.

INTO tstudent -> Destination table.

(sid, name , phone) -> Attribute/destination column.

VALUES (104, ’Andi subagja’, ’08156222345’) -> First line inserted.

VALUES (105,’Angelina Marini’,’08164453647’)  -> Second line inserted.

SELECT * FROM dual -> The selection of attributes from the dual table (dummy), can also attribute options from other existing tables.

; (colon) -> Denotes the end of a SQL command.

5.       INSERT with Date Function

Attributes on a table with DATE data type have special treatment when receiving INSERT command. The special treatment is the determination of the format of the date when the user typed a date value. The value format needs to be defined so that the INSERT process into the DATE attribute is successful because the date format may vary from one computer to another (the order of DD MM YYYY). Provision of date format is done by utilizing the TO_DATE function.

Format :

INSERT INTO <table_name> (<attribute(s)>)

VALUES (TO_DATE(<value>,<date format>));

Example :

ALTER TABLE tstudent ADD datebirth DATE;

INSERT INTO tstudent (sid, name, phone, datebirth)

VALUES (106, ’Indra Birawa’, ’08153453456’, TO_DATE(’12-10-2012’,’DD-MM-YYYY’));

Explanations :

ALTER TABLE tstudent ADD datebirth DATE; -> Add datebirth attribute with DATE data type to tstudent’s table.

INSERT INTO tstudent -> Table destination of insert process.

(sid, name, phone, tlahir) -> Destination attribute in the tstudent table.

VALUES (106, ’Indra Birawa’, ’08153453456’, TO_DATE(’12-10-2012’,’DD-MM-YYYY’)); -> Value in input into tstudent table.

TO_DATE(’12-10-2012’,’DD-MM-YYYY’) -> The date input format to the datebirth attribute.

; (colon) -> Denotes the end of a SQL command.

Well that's our discussion of the 5 groups of DML INSERT commands. The examples given are simple examples, but they are the foundations that the reader needs to know. If the base has been mastered, then the reader will not have difficulty when finding a bigger case.

Thank You, Bobsis.