Previous Next JavaScript must be enabled to correctly display this content

## Inserting rows with an MR_COUNTER column

While data is inserted in a multi-region table with an MR_COUNTER column, the system generates a default value of zero for the MR_COUNTER column value in the following two cases.

- When the keyword `DEFAULT` is used in the INSERT clause for the MR_COUNTER column.
- When the MR_COUNTER column is skipped in the INSERT clause.

Specifying DEFAULT clause while inserting data into an MR_COUNTER column

Example 7-15 Insert a row to a multi-region table using the DEFAULT clause

Create a multi-region table in DEN and LON regions with an MR_COUNTER:

    CREATE Table myTable( name STRING,
                          count INTEGER AS MR_COUNTER,
                          PRIMARY KEY(name)) IN REGIONS DEN,LON

Insert a row to the table by specifying the DEFAULT clause to the counter:

    INSERT INTO myTable VALUES ("Bob", DEFAULT)

Fetch the counter value:

    SELECT * FROM myTable

Output:

    {"name":"Bob","count":0}

Skipping the MR_COUNTER column while inserting data into a multi-region table

Example 7-16 Insert a row to a multi-region table without specifying a value to the MR_COUNTER

Create a multi-region table in DEN and LON regions with an MR_COUNTER:

    CREATE Table myTable( name STRING,
                          count INTEGER AS MR_COUNTER,
                          PRIMARY KEY(name)) IN REGIONS DEN,LON

Insert a row to the table by specifying the column names, without specifying the counter or supplying any value to it:

    INSERT INTO myTable(name) VALUES ("Chris')

Fetch the table data. Notice that the system automatically inserts a zero value to the counter.

    SELECT * FROM myTable

Output:

     {"name":"Chris","count":0}

Error when MR_COUNTER column is not skipped and no DEFAULT clause is given

While inserting all column values to a row, you must specify the DEFAULT clause for the MR_COUNTER column. Else, the system displays an error.

Example 7-17 Insert all columns in a row without specifying a value to the MR_COUNTER

Create a multi-region table in DEN and LON regions with an MR_COUNTER:

    CREATE Table myTable( name STRING,
                          count INTEGER AS MR_COUNTER,
                          PRIMARY KEY(name)) IN REGIONS DEN,LON

Insert all the columns in a row. Notice that you don't supply a DEFAULT value to the counter.

    INSERT INTO myTable VALUES ("Chris")

Output:

    Error handling command execute 'INSERT INTO myTable VALUES ("Chris")':
    Error: at (1, 0) The number of VALUES expressions is not equal to the number of table columns

You cannot insert values into an MR_COUNTER column explicitly. A DEFAULT construct must always be used or the MR_COUNTER column should be skipped in the INSERT clause. If you try to insert values into the MR_COUNTER column using the INSERT clause or using API, an error is thrown as shown below.

Example 7-18 Insert a row to a multi-region table by specifying a value other than DEFAULT to the MR_COUNTER

    CREATE Table myTable ( name STRING,
                           count INTEGER AS MR_COUNTER,
                           PRIMARY KEY(name)) IN REGIONS DEN

    INSERT INTO myTable VALUES("Tom",0)'

Output:

    Error handling command execute 'INSERT INTO myTable VALUES("Tom",0)': Error: at (1, 38) MRCounter column must use DEFAULT construct.

Inserting rows into a JSON column having MR_COUNTER data type:

When inserting a row into the multi-region table with a JSON MR_COUNTER, you must supply a value zero to the MR_COUNTER.

Note:

- The system initially assigns a value of zero to all MR_COUNTER data types even if you explicitly supply a non-zero value. This also holds good when you try to provide a value that is not an INTEGER or LONG or NUMBER.
- You can't supply the keyword DEFAULT while inserting a JSON MR_COUNTER.
- The system will return an error if you try to insert data into an MR table without supplying a value to the declared JSON MR_COUNTER field or using the keyword DEFAULT.

Example 7-19 Insert data into multi-region table with a JSON MR_COUNTER

    INSERT INTO demoJSONMR VALUES ("Anna",
        {
            "id" : 1,
            "counter" : 0,
            "person" : {
                "age" : 10,
                "count" : 0,
                "number" : 100
            }
        }
    )

    SELECT * FROM demoJSONMR

Output:

    {"name":"Anna","jsonWithCounter":{"id" : 1,"counter":0,
                                     "person":{"age":10,"count":0,"number":100}
                    }
    }