Confluence has been updated to version 6.15.9

Page tree
Skip to end of metadata
Go to start of metadata
This page details out how to insert data into a grid table using database import.

Table Grid Editor for Jira Server stores data in a separate database. You can insert data into a grid using grid edit on the issue view on insert directly into the database. 

When adding a data source specification, you can select one of the supported databases.  Depending on this choice different parameters can be provided. 

In order to insert data directly into the database you should use sql query.

Prerequisites to insert data into the database

  • database name
  • TGE related issueId

Tips to get database name and TGE related issueId:

  • in Oracle and PostgreSQL use a hibernate_sequence to generate the ids for the row 
  • Database name rule : [nameOfTable]_c[fieldConfigId]
  • Ensure that the modified field is set to '0' indicating that the row was not modified yet

We use the default TGE configuration

gd.columns=isummary,iassignee,istatus,idue
gd.tablename=actions
gd.ds=jira


Examples of the queries

MySql
INSERT INTO
  actions_c10109(
    issueid,
    modified,
    isummary,
    iassignee,
    iassignee_name,
    istatus,
    istatus_name,
    idue
  )
VALUES(
    10000,
    0,
    'First summary',
    'keanu',
    'Keanu Reeves',
    'O',
    'Open',
    '2018-03-06'
  ),
  (
    10000,
    0,
    'Second summary',
    'keanu',
    'Keanu Reeves',
    'D',
    'Done',
    '2018-04-23'
  );


PostgreSQL
INSERT INTO
  "actions_c10315"(
    id,
    issueid,
    modified,
    isummary,
    iassignee,
    iassignee_name,
    istatus,
    istatus_name,
    idue
  )
VALUES(
    nextval('HIBERNATE_SEQUENCE'),
    10001,
    0,
    'First summary',
    'jhon',
    'Jhon Wick',
    'O',
    'Open',
    '2018-03-06'
  ),
  (
    nextval('HIBERNATE_SEQUENCE'),
    10001,
    0,
    'Second summary',
    'jhon',
    'Jhon Wick',
    'D',
    'Done',
    '2018-04-23'
  );

This results in the table values for issue with id 10001:

OracleDB
INSERT INTO
  "ACTIONS_C10109"(
    id,
    issueid,
    modified,
    isummary,
    iassignee,
    iassignee_name,
    istatus,
    istatus_name,
    idue
  )
VALUES(
    HIBERNATE_SEQUENCE.nextval,
    10000,
    0,
    'First summary',
    'johnny',
    'Johnny Depp',
    'O',
    'Open',
    TO_DATE('2018-03-06', 'yyyy-mm-dd')
  )

MsSql 2016
INSERT INTO
  ACTIONS_C10109(
    issueid,
    modified,
    isummary,
    iassignee,
    iassignee_name,
    istatus,
    istatus_name,
    idue
  )
VALUES(
    10002,
    0,
    'First summary',
    'jack',
    'Jack Dow',
    'O',
    'Open',
    convert(date, '06-03-2018', 5)
  ),
  (
    10002,
    0,
    'Second summary',
    'jack',
    'Jack Dow',
    'D',
    'Done',
    convert(date, '13-03-2018', 5)
  );

Check connecting the grid to a database for more details.