Oracle Data insertion

Oracle Data insertion

Syntax structure: insert into Table Name (column name 1, column name 2 ......) VALUES (value 1, value 2 ......)

Code Demonstration:

SQL> INSERT INTO INFOS VALUES (①
2 's10010', 'lin chong', 'male', 22, 2,
3 TO_DATE ('2017-8-9 06:30:10 ', 'YYYY-MM-DD HH24: MI: ss'), ②
4, default, '000000' ③
5 );
Code parsing:
① The table name is followed by the default column name. The default column name is all. The values in values correspond to the sequence and Data Type of the columns in the table.
If one SQL * Plus statement can be written in multiple rows, sqlplus will give a row number before each row starting from the second row.
② In Oracle, the date is internationalized. Databases installed in different regions have different default date formats. Therefore, to facilitate program migration,
To input a date, use the TO_DATE function to format the date and then use the format string to format the date,
The characters in the formatted string are case-insensitive. Common formatting characters are as follows:
Yyyy indicates the four-digit year; mm indicates the two-digit month; dd indicates the date; hh24 indicates the hour from 1-23; hh12 also indicates the hour from 0-11;

Mi indicates minute; ss indicates second
③ When a column with a default value exists, use the default value instead.
In Oracle, an INSERT command can INSERT a result set to a table at a time.

The statement used is: insert into Table SELECT clause

Code Demonstration: INSERT a result set to a table

SQL> INSERT INTO INFOS2 SELECT * FROM INFOS;

In this syntax, the Data Type of each column in The result set must be the same as that of each column in the table. The number of columns in the result set must be the same as that in the table.