Change: ‘loadUpdateData’

Loads or updates data from a CSV file into an existing table. Differs from loadData by issuing a SQL batch that checks for the existence of a record. If found, the record is UPDATEd, else the record is INSERTed. Also, generates DELETE statements for a rollback.

A value of NULL in a cell will be converted to a database NULL rather than the string ‘NULL’

Available Attributes

NameDescriptionRequired ForSupportsSince
catalogNameName of the catalogall3.0
encodingEncoding of the CSV file (defaults to UTF-8)all
fileCSV file to loadallall
primaryKeyComma delimited list of the columns for the primary keyallall
quotcharall
schemaNameName of the schemaall
separatorall
tableNameName of the table to insert or update data inallall

Nested Properties

NameDescriptionRequired ForSupportsMultiple AllowedSince
columnsDefines how the data should be loaded.

See the column tag documentation for more information
allallyes
<changeSet author="liquibase-docs" id="loadUpdateData-example">
    <loadUpdateData catalogName="cat"
            encoding="UTF-8"
            file="com/example/users.csv"
            primaryKey="pk_id"
            quotchar="A String"
            schemaName="public"
            separator="A String"
            tableName="person">
        <column name="address" type="varchar(255)"/>
    </loadUpdateData>
</changeSet>
changeSet:
  id: loadUpdateData-example
  author: liquibase-docs
  changes:
  - loadUpdateData:
      catalogName: cat
      columns:
      - column:
          name: address
          type: varchar(255)
      encoding: UTF-8
      file: com/example/users.csv
      primaryKey: pk_id
      quotchar: A String
      schemaName: public
      separator: A String
      tableName: person
{
  "changeSet": {
    "id": "loadUpdateData-example",
    "author": "liquibase-docs",
    "changes": [
      {
        "loadUpdateData": {
          "catalogName": "cat",
          "columns": [
            {
              "column": {
                "name": "address",
                "type": "varchar(255)"
              }
            }]
          ,
          "encoding": "UTF-8",
          "file": "com/example/users.csv",
          "primaryKey": "pk_id",
          "quotchar": "A String",
          "schemaName": "public",
          "separator": "A String",
          "tableName": "person"
        }
      }]
    
  }
}

SQL Generated From Above Sample (MySQL)

INSERT INTO cat.person (`id,
 name,
 age`) VALUES ('1,
 Fred,
 21')
ON DUPLICATE KEY UPDATE id,
 name,
 age = '1,
 Fred,
 21'

INSERT INTO cat.person (`id,
 name,
 age`) VALUES ('2,
 Wilma,
 22')
ON DUPLICATE KEY UPDATE id,
 name,
 age = '2,
 Wilma,
 22'

INSERT INTO cat.person (`id,
 name,
 age`) VALUES ('3,
 Barney,
 42')
ON DUPLICATE KEY UPDATE id,
 name,
 age = '3,
 Barney,
 42'

Database Support

DatabaseNotesAuto Rollback
DB2SupportedNo
DerbySupportedNo
FirebirdSupportedNo
H2SupportedNo
HyperSQLSupportedNo
InformixSupportedNo
MySQLSupportedNo
OracleSupportedNo
PostgreSQLSupportedNo
SQL ServerSupportedNo
SQLiteSupportedNo
SybaseSupportedNo
Sybase AnywhereSupportedNo

Missing, incomplete or wrong information? Please send a pull request