Oracle insert into syntax

Oracle insert into syntax

Basically, we have two ways to input data into the table. One is input at a time, and the other is input at a time. Let's take a look at the input method.

Follow the Convention to introduce the syntax first. Syntax:

Insert into "table name" ("column 1", "column 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 (10 BYTE ),
4 Last_Name VARCHAR2 (10 BYTE ),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number (8, 2 ),
8 City VARCHAR2 (10 BYTE ),
9 Description VARCHAR2 (15 bytes)
10)
11/

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01 ', 'jason', 'martin ', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19960725, 'toronto ', 'programmer ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('02 ', 'alison', 'mathews ', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19760321, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('03', 'James ', 'Smith', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19781212, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('04 ', 'cela', 'Rice', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19821024, 'vancouver ', 'manager ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('05 ', 'Robert', 'black', to_date ('000000', 'yyyymmdd'), to_date ('20140901', 'yyyymmdd'), 19840115, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('06', 'linda ', 'green', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19870730, 'New York ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('07 ', 'David', 'Larry', to_date ('123', 'yyyymmdd'), to_date ('123456', 'yyyymmdd'), 19901231, 'New York ', 'manager ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('08', 'James ', 'cat', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19960917, '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
-----------------------------------------------------------------------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-apr-01232.78 Vancouver Tester

Instance 2

SQL> -- create demo table
SQL> create table Employee (
2 ID VARCHAR2 (4 BYTE) not null,
3 First_Name VARCHAR2 (10 BYTE ),
4 Last_Name VARCHAR2 (10 BYTE ),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number (8, 2 ),
8 City VARCHAR2 (10 BYTE ),
9 Description VARCHAR2 (15 bytes)
10)
11/

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee
2 values ('01 ', 'jason', 'martin ', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19960725, '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
-----------------------------------------------------------------------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer

Insert a specified value

SQL> CREATE TABLE myTable (
2 name VARCHAR2 (25) not null,
3 price NUMBER (4, 2) 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