DB2 - SQL Insert Into Statement

The INSERT INTO command adds new rows to a table.

There are two ways to write the INSERT INTO statement.

The first way specifies both the column names and the values to be inserted. The syntax is,

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

Second way, If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.

The INSERT INTO syntax would be as follows.

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

Example 1:

The table TB_DEPT contains the following columns:

• DEPT_NO
• DEPT_NAME
• DEPT_MGR_NO
• DEPT_ADMR.

Insert a new department with the following specifications into the TB_DEPT table.

• Department number (DEPT_NO) is 'A31'
• Department name (DEPT_NAME) is 'ARCHITECTURE'
• Managed by (DEPT_MGR_NO) a person with number '00123'
• Reports to (DEPT_ADMR) department 'A01'.

INSERT INTO TB_DEPT
    VALUES (‘A31’
    , ‘ELECTRICAL’
    , ‘00123’
    , ‘A01’);

In the above SQL query, There is no column name because you are adding values for all the columns of the table.

As we said before, If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.

Result:

Below is a selection from the "TB_DEPT" table in the DB2 database.

Example 2:

To insert a new department into the TB_DEPT table as in example 1, but without manager number to the new department.

INSERT INTO TB_DEPT (DEPT_NO
      , DEPT_NAME
      , DEPT_ADMR )
    VALUES (‘A31’
      , ‘ELECTRICAL’
      , ‘A01’);

In this example, You need to mention the column. because you are only inserting the data into specific columns.

Result:

Below is a selection from the "TB_DEPT" table in the DB2 database.

NULL value inserted into "TB_DEPT" table for the column DEPT_MGR_NO

To learn more about NULL value. Go to NULL value chapter.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!