Oracle INSERT INTO syntax

Oracle INSERT INTO syntax

Basically, there are two ways we can enter the data into the table. One is to enter a pen at a time, the other is to enter several pens at a time. Let's look at a way to enter a pen first.

By convention, we'll introduce grammar first. The syntax for entering one piece of data at a time is as follows:

INSERT into "table name" ("Field 1", "Field 2", ...)
VALUES ("Value 1", "Value 2", ...)

Instance

Sql>--Create demo table
Sql> CREATE TABLE Employee (
2 ID VARCHAR2 (4 BYTE) not NULL,
3 first_name VARCHAR2 (BYTE),
4 last_name VARCHAR2 (BYTE),
5 start_date Date,
6 end_date Date,
7 Salary Number (8,2),
8 City VARCHAR2 (BYTE),
9 Description VARCHAR2 (MB)
10)
11/

Table created.

Sql>
Sql>--Prepare data
sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Jason ', ' Martin ', to_date (' 19960725 ', ' YYYYMMDD '), to_date (' 20060725 ', ' YYYYMMDD '), 1234.5 6, ' Toronto ', ' Programmer ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Alison ', ' Mathews ', To_date (' 19760321 ', ' YYYYMMDD '), to_date (' 19860221 ', ' YYYYMMDD '), 6661.7 8, ' Vancouver ', ' Tester ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (', ' James ', ' Smith ', to_date (' 19781212 ', ' YYYYMMDD '), to_date (' 19900315 ', ' YYYYMMDD '), 6544.7 8, ' Vancouver ', ' Tester ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Celia ', ' Rice ', to_date (' 19821024 ', ' YYYYMMDD '), to_date (' 19990421 ', ' YYYYMMDD '), 2344.7 8, ' Vancouver ', ' Manager '
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Robert ', ' Black ', to_date (' 19840115 ', ' YYYYMMDD '), to_date (' 19980808 ', ' YYYYMMDD '), 2334.7 8, ' Vancouver ', ' Tester ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Linda ', ' Green ', to_date (' 19870730 ', ' YYYYMMDD '), to_date (' 19960104 ', ' YYYYMMDD '), 4322.7 8, ' New York ', ' Tester '
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' Modified ', ' David ', ' Larry ', to_date (' 19901231 ', ' YYYYMMDD '), to_date (' 19980212 ', ' YYYYMMDD '), 7897.7 8, ' New York ', ' Manager '
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (', ' James ', ' Cat ', to_date (' 19960917 ', ' YYYYMMDD '), to_date (' 20020415 ', ' YYYYMMDD '), 1232.7 8, ' Vancouver ', ' Tester ')
3/

1 row created.

Sql>
Sql>
Sql>
Sql>--Display data in the table
Sql> SELECT * from Employee
2/

ID first_name last_name start_dat end_date SALARY City DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
Jason Martin 25-jul-96 25-jul-06 1234.56 Toronto Programmer
Alison Mathews 21-mar-76 21-feb-86 6661.78 Vancouver Tester
James Smith 12-dec-78 15-mar-90 6544.78 Vancouver Tester
Celia Rice 24-oct-82 21-apr-99 2344.78 Vancouver Manager
Robert Black 15-jan-84 08-aug-98 2334.78 Vancouver Tester
Linda Green 30-jul-87 04-jan-96 4322.78 New York Tester
David Larry 31-dec-90 12-feb-98 7897.78 New York Manager
James Cat 17-sep-96 15-apr-02 1232.78 Vancouver Tester

Example Two

Sql>--Create demo table
Sql> CREATE TABLE Employee (
2 ID VARCHAR2 (4 BYTE) not NULL,
3 first_name VARCHAR2 (BYTE),
4 last_name VARCHAR2 (BYTE),
5 start_date Date,
6 end_date Date,
7 Salary Number (8,2),
8 City VARCHAR2 (BYTE),
9 Description VARCHAR2 (MB)
10)
11/

Table created.

Sql>
Sql>--Prepare data
sql> INSERT INTO Employee
2 values (' ', ' Jason ', ' Martin ', to_date (' 19960725 ', ' YYYYMMDD '), to_date (' 20060725 ', ' YYYYMMDD '), 1234.5 6, ' Toronto ', ' Programmer ')
3/

1 row created.

Sql>
Sql>
Sql>
Sql>--Display data in the table
Sql> SELECT * from Employee
2/

ID first_name last_name start_dat end_date SALARY City DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
Jason Martin 25-jul-96 25-jul-06 1234.56 Toronto Programmer

Insert specified value

Sql> CREATE TABLE myTable (
2 name VARCHAR2 not NULL,
3 Price Number (4,2) is not NULL,
4 start_date date);

Table created.

Sql>
Sql>
Sql>
Sql> INSERT into myTable (name, Price) VALUES (' Product name 2 ', 2.5);

1 row created.

Sql> INSERT into myTable (name, Price) VALUES (' Product name 3 ', 50.75);

1 row created.

Sql> INSERT into myTable (price, name) VALUES (99.99, ' Product name 4 ');

1 row created.

Sql>
Sql>
Sql> SELECT * from myTable;

NAME Price Start_dat
------------------------- ---------- ---------
Product Name 2 2.5
Product Name 3 50.75
Product Name 4 99.99