INSERT a Row INTO a Table

The Oracle SQL command for inserting rows into a table is the INSERT statement. Primarily, it adds one row at a time. The basic syntax of the INSERT statement is as follows:

INSERT INTO table_name (column1, column2, column3, ...) 
VALUES (value1, value2, value3, ...);

Here's a breakdown of the syntax elements:

• INSERT INTO: This clause is used to specify the table where you want to insert data.
• table_name: Replace this with the name of the table into which you want to insert data.
• (column1, column2, column3, ...): Specify the columns in the table where you want to insert data. If you're inserting data into all columns, you can omit this part.
• VALUES: This keyword is followed by a list of values enclosed in parentheses. The order of the values should correspond to the order of the columns specified earlier.
• (value1, value2, value3): Provide the values to be inserted into the respective columns. Ensure that the data types and order of values match the columns specified earlier.

Let's look at an example of an INSERT statement. We're going to add a row to the my_pokemon table. First, let's describe the table so that we can see the columns - we'll need to know the names data types, and/or any constraints specified in the columns, so that we can build our INSERT statement.

The table has three columns: pokemon_id, pokemon_level, and caught_at. The pokemon_id and pokemon_level take numeric values, whereas caught_at takes timestamp value as its input. Currently, we don't have any specific constraints on the table. 

With the information, we can add a row into the table as follows:

Since we're inserting a row to all the columns in the table, we can omit the column list in the INSERT statement:

When attempting to insert a row with certain missing values, it is necessary to explicitly state the column names to which you are assigning values. The column names listed in the INSERT statement does not necessarily have to match their storage order. However, the specification of column names must align with the values in the INSERT statement. For example:

While you're not required to have column names, when you insert rows into all columns in the table, I would recommend you to explicitly list column names in the statement. One of the reasons is that this approach will keep your code executable, even if the table structure is altered later. For example, let's say that you add one more column in the table. In this situation, your code without column list will no longer be executable. This is because Oracle now expects one more value to be listed in the INSERT statement VALUES clause. However, if you have enumerated column lists in the INSERT statement, your code will still be executable. It will leave the new column value NULL, but at least Oracle will not throw an error.

Data Type Conversion

Values listed in the INSERT statement must be compatible to the column data types. Otherwise, Oracle will throw a syntax error. While it is advisable to avoid any implicit type conversions, Oracle will automatically perform type conversion whenever feasible. For example, in case where you input a character string, such as '254', into a data type like NUMBER, Oracle execute an implicit data type conversion, transforming the character string to a number and the statement will be successful. For example:

Of course, it cannot convert a character string like 'Pikachu' into a number. But, again, relying on implicit data type conversion is strongly discouraged and you should avoid it as much as possible. This is not only because implicit type conversion can lead to unexpected results and errors in your code, but also make your code less readable.

INSERT and Constraints

In practice, many tables include constraints, and if you happen to have any value violating any constraint, it may result in a runtime error. To illustrate, consider a table below:

In the table creation, I added a NOT NULL constraint to pokemon_id column. Then I intently violated the constraint in the INSERT statement shown below: