---
title: "DB2: 4 Top INSERT SQL Queries"
description: "Top questions on DB2 SQL insert values and how to find errors explained."
url: https://srinimf.com/2016/01/29/db2-error-while-inserting-a-record-it-is-not-duplicate-record-why/
date: 2016-01-29
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2016/01/pexels-photo-4686824.jpeg?fit=1880%2C1253&ssl=1
categories: ["DB2", "DB2 V11"]
tags: ["SQL tips and tricks"]
type: post
lang: en
---

# DB2: 4 Top INSERT SQL Queries

Here are four SQL INSERT Queries you can use in DB2. Also, explained [how to](https://srinimf.com/2021/10/16/how-to-resolve-206-sql-error-object-not-valid-in-db2/) get diagnostics of inserted rows.

---

**1). What are basic rules of INSERT in DB2?**

- The INSERT statement inserts rows into a table or view or activates the INSTEAD OF INSERT trigger.
- The table or view can be at the current server or any DB2® subsystem with which the current server can establish a connection.
- Inserting a row into a view inserts the row into the table on which the view is based if no INSTEAD OF INSERT trigger is defined on the specified view.
- If an INSTEAD OF INSERT trigger is defined, the trigger is activated instead of the INSERT statement.

## **Four SQL INSERT Queries**

---

1). The **INSERT via VALUES** form is used to insert a single row into the table or view using the values provided or referenced.

2). The **INSERT via SELECT **form is used to insert one or more rows into the table or view using values from other tables, or views, or both.

3). The **INSERT via FOR n ROWS** form is used to insert [multiple rows in](https://srinimf.com/2022/07/25/top-sql-queries-to-insert-single-row-and-multi-rows/)to the table or view using values provided or referenced. Although not required, the values can come from host-variable arrays.

4) This form of INSERT is supported [in SQL](https://srinimf.com/2013/09/06/db2-multi-row-insert-into-a-table/) procedure applications. However, since host-variable arrays are not supported in SQL procedure applications, the support is limited to insertion of scalar values.

**Syntax for INSERT**

```
EXEC SQL
Insert into table_name
Values (:v1,:v2)
END-EXEC
```

**2). What are different issues with multiple row insert?**

ATOMIC or NOT ATOMIC CONTINUE ON SQLEXCEPTION Specifies whether all of the rows should be inserted as an atomic operation or not.

**ATOMIC** Specifies that if the insert for any row fails, all changes made to the database by any of the inserts, including changes made by successful inserts, are undone. This is the default **(ATOMIC key word you can use only in INSERT SQL query)**

*Example Query to insert multiple rows:*

```
EXEC SQL INSERT INTO T1 
FOR :hv ROWS 
VALUES (:hva:hvind) ATOMIC;
```

**NOT ATOMIC CONTINUE ON SQL EXCEPTION**

Specifies that, regardless of the failure of any particular insert of a row, the INSERT statement will not undo any changes made to the database by the successful inserts of other rows, and inserting will be attempted for subsequent rows.

However, the minimum level of atomicity is at least that of a single insert (that is, it is not possible for a partial insert to complete), including any triggers that might have been executed as a result of the INSERT statement

**3). How many rows can insert into a Table with multiple insert option?**

Host-variable or integer-constant is assigned to an integral value k. If host-variable is specified, it must be an exact numeric type with scale zero, and must not include an indicator variable. Furthermore, k must be in the range, **0k<=32767**. k rows are inserted into the target table from the specified source data. **(So, in DB2 maximum 32767 rows can be inserted at a time into a Table).**

**4). How to get INSERT diagnostics after running a Query?**

After execution of the INSERT statement, the following information will be in the SQLCA:

- `SQLCODE = -253 (An error is returned)`
- `SQLSTATE = 22529`
- `SQLERRD3 = 8 (Number of rows successfully inserted)`

During INSERT, you will come across other SQL error code (i.e., other than duplicate record) is how many records successfully inserted or how many records failed during insert.

Different diagnostics information SQL queries:

```
GET DIAGNOSTICS 
:num_rows = ROW_COUNT, 
:num_cond = NUMBER;
```

The result of this statement is num_rows = 8 and num_cond = 2 (2 conditions).

```
GET DIAGNOSTICS CONDITION 2 
:sqlstate = RETURNED_SQL
:sqlcode = DB2_RETURNED_SQLCODE,                              :row_num = DB2_ROW_NUMBER;
```

The result of this statement is sqlstate = 22003, sqlcode = -302, and **row_num = 4.**

```
GET DIAGNOSTICS CONDITION 1 
:sqlstate = RETURNED_SQLSTATE,
:sqlcode = DB2_RETURNED_SQLCODE,
:row_num = DB2_ROW_NUMBER;
```

The result of this statement is sqlstate = 22003, sqlcode = -302, and **row_num = 8.**

So, with the above SQL query, you will know what are the rows failed during insert.

Related Posts

- [How to INSERT Array of Values into DB2 Table](https://srinimf.com/2020/02/29/db2-insert-array-in-tables/)DateFebruary 29, 2020
- [DB2: What is Atomic in SQL Query](https://srinimf.com/2013/09/06/db2-multi-row-insert-into-a-table/)DateSeptember 6, 2013
- [How to Tune SQL Query 5 Ideas](https://srinimf.com/2015/02/14/5-more-db2-sql-performance-tips/)DateFebruary 14, 2015

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2016%2F01%2F29%2Fdb2-error-while-inserting-a-record-it-is-not-duplicate-record-why%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2016%2F01%2Fpexels-photo-4686824.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=DB2%3A%204%20Top%20INSERT%20SQL%20Queries)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2016/01/29/db2-error-while-inserting-a-record-it-is-not-duplicate-record-why/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2016/01/29/db2-error-while-inserting-a-record-it-is-not-duplicate-record-why/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2016/01/29/db2-error-while-inserting-a-record-it-is-not-duplicate-record-why/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2016/01/29/db2-error-while-inserting-a-record-it-is-not-duplicate-record-why/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%3A%204%20Top%20INSERT%20SQL%20Queries&body=https%3A%2F%2Fsrinimf.com%2F2016%2F01%2F29%2Fdb2-error-while-inserting-a-record-it-is-not-duplicate-record-why%2F&share=email)
-

### Like this:

Like Loading…