---
title: "COBOL: How to Code Logic To Insert Multiple Rows"
description: "Multiple rows you can insert using arrays in COBOL program into DB2 tables"
url: https://srinimf.com/2013/04/07/db2-multiple-rows-insert-with-arrays/
date: 2013-04-07
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2013/04/inserting-multiple-rows.jpg?fit=1200%2C700&ssl=1
categories: ["DB2"]
tags: ["ARRAYS", "DB2 Tips and Solutions"]
type: post
lang: en
---

# COBOL: How to Code Logic To Insert Multiple Rows

In DB2 we can insert rows in two ways. One is insert single row with Values. The second one is multi row insert.

Related[DB2 Tough Questions (4 of 4)](https://srinimf.com/2012/02/13/db2-tough-questions-4-of-4/)

Store for [Db2 books](http://astore.amazon.com/mainframesrin-20).

Arrays is the concept which was introduced in DB2 v8.

[How to](https://srinimf.com/2020/02/29/db2-insert-array-in-tables/) use arrays?

**Process:**

1. Declare variable to hold no of rows
2. Host variable declaration, write each variable in an array
3. Move value to no of rows
4. Insert statement give host variable array

Example:

```
01  HV-NUM-ROWS            PIC S9(4) COMP.
01  HOST-VARIABLES.
    05  HV-EMPNO-ARRAY PIC X(6)  OCCURS 100 TIMES.
    05  HV-LASTNAME-ARRAY OCCURS 100 TIMES.
       49  HV-LASTNAME-LENGTH  PIC S9(4) COMP.
       49  HV-LASTNAME-TEXT      PIC X(25).
    05  HV-SALARY-ARRAY       PIC S9(7)V9(2) OCCURS 100 TIMES.
    05  HV-BONUS-ARRAY    PIC S9(7)V9(2)  OCCURS 100 TIMES.
    05 HV-BONUS-NI-ARRAY PIC S9(4) COMP OCCURS 100 TIMES.
```

The program loads up the arrays with data to be inserted and then executes the following SQL statement:

```
MOVE +100 TO HV-NUM-ROWS.
INSERT INTO EMP
  (EMPNO, LASTNAME, FIRSTNME,
    MIDINIT, SALARY, BONUS)
VALUES (:HV-EMPNO-ARRAY,
        :HV-LASTNAME-ARRAY,
        :HV-SALARY-ARRAY,
        :HV-BONUS-ARRAY:HV-BONUS-NI-ARRAY)
FOR :HV-NUM-ROWS ROWS
ATOMIC
```

Atomic: Tells Db2 if an error occurs during insert, all the rows will be rolled back

NOT ATOMIC CONTINUE ON SQLEXCEPTION

It will continue insert all the rows,except erred rows.

To find rows which are not inserted, use SQL GET DIAGNOSTICS statement.

For Dynamic SQL, developers must specify For Multiple Rows during the PREPARE and For N Rows and Using for the list of variables in the EXECUTE. See the IBM manuals for further information.

Related Pots

- [DB2: What is Atomic in SQL Query](https://srinimf.com/2013/09/06/db2-multi-row-insert-into-a-table/)DateSeptember 6, 2013
- [DB2: 4 Top INSERT SQL Queries](https://srinimf.com/2016/01/29/db2-error-while-inserting-a-record-it-is-not-duplicate-record-why/)DateJanuary 29, 2016
- [How to INSERT Array of Values into DB2 Table](https://srinimf.com/2020/02/29/db2-insert-array-in-tables/)DateFebruary 29, 2020

### 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%2F2013%2F04%2F07%2Fdb2-multiple-rows-insert-with-arrays%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2013%2F04%2Finserting-multiple-rows.jpg%3Ffit%3D1200%252C700%26ssl%3D1&description=COBOL%3A%20How%20to%20Code%20Logic%20To%20Insert%20Multiple%20Rows)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2013/04/07/db2-multiple-rows-insert-with-arrays/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2013/04/07/db2-multiple-rows-insert-with-arrays/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2013/04/07/db2-multiple-rows-insert-with-arrays/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2013/04/07/db2-multiple-rows-insert-with-arrays/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20COBOL%3A%20How%20to%20Code%20Logic%20To%20Insert%20Multiple%20Rows&body=https%3A%2F%2Fsrinimf.com%2F2013%2F04%2F07%2Fdb2-multiple-rows-insert-with-arrays%2F&share=email)
-

### Like this:

Like Loading…