How to create a SQL INSERT statement

The tutorial describes how to convert a SELECT query into the INSERT VALUES and INSERT RESULTS queries using the Query Builder tool.

The INSERT VALUES statement allows adding user-defined values to the columns. However, it cannot be used when you need to add multiple values to the table. Instead, you can use the INSERT RESULTS statement to insert values from several tables into a single target table.

The tutorial describes the following:

Add user-defined values to the target table

For demo purposes, we are going to use an empty table Sales.Staff from the BicycleStoreDev database and then populate it with test data. First, open the table on the Query Builder diagram.

1. In the Database Explorer, right-click the table and select Send to > Query Builder.

Alternatively, from the Database Explorer, drag the table to the diagram.

2. In the upper-left corner of the diagram, right-click SELECT and select Change Type > Insert Values to add the values to the table. The statement has been converted to the INSERT INTO statement.

3. On the diagram, select the columns for the query. The selected columns will be automatically displayed on the Insert tab of the Tabbed Editor.

Note

If the Tabbed Editor is not displayed, you can open it by clicking Show Tabbed Editor Show Tabbed Editor on the Query toolbar or main menu.

To exclude the columns, clear the corresponding checkboxes on the diagram.

4. Insert the values to the columns. To do this, on the Insert tab of the Tabbed Editor, enter the values in the Columns fields.

Change a statement type

5. Switch to the Text view to review the designed query text.

Review the designed query text

6. Execute the statement by pressing F5 or clicking Execute to verify that the values have been inserted in the table successfully.

Review the designed query text

Add values from multiple tables to the target table

1. From the Database Explorer, select several tables and drag them to the Query Builder diagram.

2. Switch to the Where tab to add a filtering condition. To do this, click Set up a filtering condition Add and specify the condition. For example, the result should be filtered based on the criteria that SalesPersonID equals 280.

  • Select the SalesPersonID column as an operand.
  • Set equals as a criteria operator.
  • Enter 280 as a value.

Set a filtering condition

3. On the diagram, select the columns you want to add to the target table.

4. Change the query type from Select to Insert Results. To do this, right-click the diagram and select Change Type > Insert Results.

Set a filtering condition

5. In the Choose Target Table to Insert Data to dialog that opens, select the database, schema, and table you want to insert data into, and click OK.

Note

The table, query fields, and column data types must match, while column names can differ.

If they don’t match like in our example, then click the field and correct the order.

Fix the order in the columns

Select the target table into which the data will be inserted

As you can see, the query type has been changed.

View the changed query

6. Switch to the Text view to preview the SQL query script and execute the query.

Watch the video to see how to create a SQL INSERT statement using the Query Builder tool.