Creating an INSERT statement

Using an INSERT statement, you can insert rows from a values set or from a subquery result table into a database table.

The method to create both of these types of INSERT statements using the SQL Builder is described below.

See your database vendor documentation for questions concerning the syntax of INSERT statements. There may be variations in syntax depending upon the database vendor that you are using. The correct syntax for the database vendor you are using is automatically created for you by the SQL Builder or the Create A New SQL Statement wizard if you use the user interface to create your statement.

To insert a row from a set of values using the SQL Builder:

  1. Switch to the Data Definition view in the Data perspective.

  2. Expand the project or folder that contains your database, and then expand the database nodes until you see the Statements folder.

  3. Right-click the Statements folder, and then click New > Insert Statement on the pop-up menu.

  4. Type a name for the statement, and then click OK. The SQL Builder opens.

  5. Add the table into which to insert data. Right-click in the Tables pane and then click Add Table on the pop-up menu.

  6. Click the table to use, and then click OK.

  7. In the Tables pane, select one or more columns into which to insert data.

  8. In the Design pane, click the Values option for Insert rows from. The columns you selected are listed in the table.

  9. Click the cell beside the column that you want to insert data into and type the value you want to insert, or click Build Expression in the list and create an expression. If the column permits a NULL value, NULL is provided as the default value for the column. You must enter values for non-nullable columns; otherwise, you get an error at execution time.

  10. When you have finished adding values, select SQL > Execute to execute the statement. A message in the DB Output view indicates whether the statement executed successfully.

To insert a row from a subquery using the SQL Builder:

  1. Switch to the Data Definition view in the Data perspective.

  2. Expand the project or folder that contains your database, and then expand the database nodes until you see the Statements folder.

  3. Right-click the Statements folder, and then click New > Insert Statement on the pop-up menu.

  4. Type a name for the statement, and then click OK. The SQL Builder opens.

  5. Add the table into which to insert data. Right-click in the Tables pane and then click Add Table on the pop-up menu.

  6. Click the table to use, and then click OK.

  7. In the Tables pane, select one or more columns into which to insert data.

  8. In the Design pane, click the Subquery option for Insert rows from.

  9. In the Query name list, click the query to use or choose to create a new one by clicking the appropriate Add ... selection in the list.

  10. If you created a new subquery, expand the statement in the Outline view, select the subquery and complete it in the SQL Builder. For more information on how to complete your subquery, see the related tasks below on SELECT, FULLSELECT and WITH statements.

  11. Select the INSERT statement from the Outline view to go back to your INSERT statement.

  12. When you have finished your statement, click SQL > Execute to execute the statement. The output is shown in the DB Output view.

To create an INSERT statement using the Create A New SQL Statement wizard:

  1. Click File > New > Other.

  2. In the New window, expand the Data folder, click SQL Statement, and then click Next.

  3. In the SQL statement field of the wizard, click INSERT.

  4. Fill in the remaining fields on the wizard pages, as necessary. To see information about a particular field, position the pointer in the field and then press F1.

INSERT statement
You can use an INSERT statement to insert rows from a values set or from a subquery result table into a database table.

 

Parent topic

SELECT, UPDATE, INSERT and DELETE statements. The SQL Builder also supports WITH (DB2 only) and FULLSELECT statements. Any statement that you create is stored in a file with the extension .sqx.">Creating an SQL statement

 

Related tasks

Editing an SQL statement
Executing an SQL statement