Inserting Data into Oracle Tables in Oracle via SQL-Developer
To insert data into Oracle tables using SQL-Plus, you must be logged on to the server. Data can be added to tables via the INSERT statement.
Remember that this inserts data one row at a time. You can create your INSERT statements in notepad or other similar editor, and copy them into the SQL-Plus editor. You do have to issue the run command for each insertion.
Since your data for the most part does not include missing values, you should be able to use the INSERT statement, using only the VALUES clause, and not specify all columns. When setting up your INSERT statements, remember that
- character data should be delimited by single quotes
- numeric data should not be delimited
- null values can be entered using NULL (not quoted)
- dates should be entered using a DD-MON-YY format with single quotes, or via a TO_DATE function
- all values to be separated by commas
The following example illustrates how to enter dates in the DD-MON-YY format.
Alternatively, if you wish to enter dates in a more conventional format, you must use the TO_DATE function, and specify a format, as illustrated below.
To enter dates with times, you must use the TO_DATE function, as in:
- Minutes are designated as ‘MI’ and not ‘MM’ (which is reserved for months). In case you need to use seconds, then these are designated as ‘SS’.
- Hours are on a 1-12 scale and may need an AM or PM designator to signify the appropriate meridian. You can get around this by using a 24 hour clock, as illustrated below.
