Insert Data Into Table SQL

Welcome back readers on this database learning website, this article will tells about Insert Data Into Table SQL. In an earlier article entitled Data Definition Language (DDL) has been presented about the introduction of Data Definition Language (DDL), where one of the DDL capabilities is that it can be used to create tables in a database. The table itself is a major component of relational databases such as Oracle, SQL Server and MySQL, without tables hence there is no such thing as a relational database.

In the article entitled Table Introduction In Database has given example of table form as follows:

ID	NAME
101	This is record number One
102	Here is the Second record
103	Three is after Two

The table has 2 columns/attributes/fields and 3 rows/records data. Suppose the table we named TDUMMY, then to make it using the DDL command is as follows:

CREATE TABLE tdummy(

                id NUMBER,

                name VARCHAR2(32),

CONSTRAINT pk_tdummy PRIMARY KEY(id)

);

That’s easy right to create table with DDL command?

Okay before we go into the DML command let's review the DDL command in the following DDL article:

CREATE TABLE TStudent (

sid NUMBER NOT NULL,

name VARCHAR(64),

phone VARCHAR(16),

CONSTRAINT pk_student PRIMARY KEY (sid)

);

If we create an illustration of the TSTUDENT table form will be as follows:

SID	NAME	PHONE
101	One	111111

Well this is the form that should be in our mind as we create the table with DDL command. Similarly when we will do input data to the table. The process of inputting data into the database table using a specific command called Data Manipulation Language (DML). DML is a variant of Structured Query Language (SQL) as well as DDL. Basically DML is used to perform data addition, data change, data deletion and data appearance.

In general DDL and DML commands are the same in all databases because they are standardized in the International Organization for Standardization (ISO). The DML command itself is basically 4 INSERT-SELECT-UPDATE-DELETE or CREATE-READ-UPDATE-DELETE (CRUD). Let's discuss the four DML commands in more detail by giving examples of their use, but in this article we will discuss INSERT's command first in depth. Need to be reminded of the database that the author uses is the Oracle database, if the reader uses a different database may need to have syntax adjustment.

The INSERT command serves to add/insert one or more rows of data into one or more tables in a database. In the Oracle database, the INSERT command on its use must always end with the COMMIT command to be permanently stored in the table, otherwise it will only be stored in the current session only. In the Oracle database, we can group INSERT commands into 5 ie basic INSERT, INSERT with SELECT, INSERT with WHEN, INSERT ALL and INSERT with Date Function.

Okay now we will try to start the explanation by presenting examples of the use of the five types of INSERT command in the Oracle database.

1.       Basic INSERT

The INSERT command has a rule value (must be the value) must have the same number, data type and sequence with the attribute (column name) of the table to which it belongs. If the structure of the destination table is known for certain amount, data type and sequence then the column name / attribute can be excluded.

Format:

INSERT INTO <tabel_name> (<attribut(es)>)

VALUES (<value(s)>);

Example:

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

Explanations :

INSERT INTO tstudent -> Perform the insert command into the tstudent table.

(sidm name, phone) -> Target attributes to do the addition of data, the order of attributes really need to be considered.

VALUES (101, ‘One’, 111111) -> The value 101 is inputted into the SID attribute, the value 'One' is inputted into the name attribute, the value '111111' is inputted into the phone attribute. These three values generate a new row of data in the table.

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

Now please fill in the following data into the TSTUDENT table by using the INSERT command like the example above:

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

Thus one uses the INSERT command on DML. Explanation of other INSERT commands and other DML commands will be discussed in the next article.

Thank You, Bobsis.