Use the DML insert statement in oracle to insert data into the table. First, we will introduce the syntax that only one data entry can be inserted at a time.
Insert into Table Name (column Name List) VALUES (Value List );
Note:
When values are assigned to all columns in the table, the column name list can be omitted, and parentheses are also omitted.
You must assign values to non-empty fields in the table.
Fields with default values can be left blank. In this case, the corresponding column names in the column name list must be omitted.
Example: Table definitions are as follows:
Create table book (bookid char (10) not null, name varchar2 (60), price number (5, 3 ))
Use the following statement to insert data
Insert into book (bookid, name, price) VALUES ('2013', 'oracle SQL ', 100123 );
Insert into book values ('20170101', 'oracle SQL ', 100123 );
Insert into book (bookid) VALUES ('20140901 ');
Because the bookid is not empty, you must assign a value to the bookid at least for the book, although such data is incomplete.
If you want to insert multiple data entries into a table, the insert statement with the values clause will not work. At this time, you must use the insert statement and select statement to insert multiple data entries at the same time:
For example, a blank table a and a table B with data have the same structure. The statement to insert all data in Table B to Table a is as follows:
Insert into a (column 1, column 2, column 3)
SELECT column 1, column 2, column 3
From B;
-- Any complex condition or subquery can be used in the query statement.
If the data source is not the data of an existing table and you want to insert multiple data entries, use the following method:
Insert into tablename (column 1, column 2, column 3 ,)
SELECT value 1, value 2, value 3 FROM DUAL
UNION
SELECT value 1, value 2, value 3 FROM DUAL
UNION
SELECT value 1, value 2, value 3 FROM DUAL
If the preceding values have character and date data, you can use single quotation marks. Each select statement obtains one piece of data, and then uses the union operator to merge multiple pieces of data into one result set, to insert multiple data entries at a time.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.