INSERT is one of the SQL keywords that are part of SQL's Data Manipulation Language.
DML can manipulate data in relational database.
Let's start with the first form of an INSERT statement, INSERT...VALUES.
The following code shows how to use DML INSERT statement that uses a VALUES clause.
INSERT INTO authors ( id, name, birth_date, gender )
VALUES ( 100, 'Tom', to_date('19830823', 'YYYYMMDD'), 'MALE' );
COMMIT;
The syntax for the INSERT VALUES statement used in the code above is as follows:
INSERT INTO <table_name> (
<column_name_1>,
<column_name_2>, ...
<column_name_N> )
VALUES (
<column_value_1>,
<column_value_2>,...
<column_value_N> );
<table_name> is the name of the table you wish to INSERT VALUES INTO, <column_name>is one of the columns from the table into which you wish to insert a value <column_value> is the value to place into the corresponding column.
The COMMIT statement that follows the INSERT VALUES statement commits your inserted values in the database.
The following two insert statements adds data to author_books table.
INSERT INTO author_books ( id, author_id, title, publish_date )
VALUES ( 10, 100, 'CSS', to_date('201000101', 'YYYYMMDD') );
INSERT INTO author_books (
id, author_id, title, publish_date )
VALUES ( 20, 100, 'Learn CSS', to_date('20200101', 'YYYYMMDD') );
COMMIT;
execute SYS.DBMS_STATS.gather_table_stats(USER, 'AUTHOR_PUBLICATIONS');
There's a second form of the INSERT statement that can be quite useful.
The second form of an INSERT statement uses a SELECT statement instead of a list of column values.
The following code shows how to insert data entry using the INSERT ... SELECT syntax.
INSERT INTO authors (
id, name, birth_date, gender )
SELECT 200, 'Jack', to_date('19410101', 'YYYYMMDD') 'MALE'
FROM dual;
COMMIT;
The syntax for the INSERT SELECT statement is as follows:
INSERT INTO <table_name> (
<column_name_1>,
<column_name_2>, ...
<column_name_N> )
SELECT <column_value_1>,
<column_value_2>,...
<column_value_N>
FROM <from_table_name> ...;
<table_name> is the name of the table you wish to INSERT INTO <column_name>is one of the columns from the table into which you wish to insert a value <column_value> is the value to place into the corresponding column <from_table_name> is the table or tables from which to select the values.
The following code shows the Conditional INSERT ... SELECT Statement.
INSERT INTO authors ( id, name, birth_date, gender )
SELECT 300, 'Jane', to_date('19830101', 'YYYYMMDD')09 'MALE'
FROM dual d
WHERE not exists ( SELECT 1 FROM authors x WHERE x.id = 300 );
COMMIT;
The subquery in the SQL SELECT statement checks to see if the desired entry already exists in the database.
If it does, Oracle does not attempt the INSERT; otherwise, it adds the row.
dual is a table owned by the oracle database (owner SYS) that has one column and one row.
To evaluate the addition of 1 + 1, execute the following SQl SELECT statement:
SELECT 1 + 1 FROM dual;
To quickly figure out how oracle will evaluate your use of the built-in function length().
SELECT length(NULL) FROM dual;
SELECT nvl(length(NULL), 0) FROM DUAL;
dual table allows you to hack away without any huge commitment in code.
The following code shows the DML for Inserting Date's Publications.
INSERT INTO author_books (
id, author_id, title, publish_date ) -- w ww.jav a 2s . c o m
VALUES ( 30, 200, 'Database', to_date('20030101', 'YYYYMMDD') );
INSERT INTO author_books (
id, author_id, title, publish_date )
VALUES ( 40, 200, 'Learn SQL', to_date('20000101', 'YYYYMMDD') );
INSERT INTO author_books (
id, author_id, title, publish_date )
VALUES ( 50, 200, 'Learn HTML', to_date('20020101', 'YYYYMMDD') );
INSERT INTO author_books (
id, author_id, title, publish_date )
VALUES ( 60, 200, 'Learn Database', to_date('20050101', 'YYYYMMDD') );
COMMIT;
The following code shows the DML for Inserting Darwen's Publications.
INSERT INTO author_books ( id, author_id, title, publish_date )
SELECT 70, 300, 'Learn SQL', to_date('20000101', 'YYYYMMDD')
FROM dual -- www . j ava 2 s . c o m
where not exists ( SELECT 1 FROM author_books x WHERE x.author_id = '300' AND x.title = 'Learn SQL' );
INSERT INTO author_books ( id, author_id, title, publish_date )
SELECT 80, 300, 'Learn HTML', to_date('20020101', 'YYYYMMDD')
FROM dual
where not exists ( SELECT 1 FROM author_books x WHERE x.author_id = '300' AND x.title = 'Learn HTML' );
COMMIT;
execute SYS.DBMS_STATS.gather_table_stats(USER, 'AUTHOR_PUBLICATIONS');