Liquibase® version 3.8.1 is now available! Get it for free.
2018 XML Announcement

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
commentLineStartsWithall
encodingEncoding of the CSV file (defaults to UTF-8)all
fileCSV file to loadallall
onlyUpdateIf true, records with no matching database record should be ignoredall3.3
primaryKeyComma delimited list of the columns for the primary keyallall
quotcharall
relativeToChangelogFileall
schemaNameName of the schemaall
separatorall
tableNameName of the table to insert or update data inallall
usePreparedStatementsall

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"
        objectQuotingStrategy="LEGACY">
    <loadUpdateData catalogName="cat"
            commentLineStartsWith="A String"
            encoding="UTF-8"
            file="com/example/users.csv"
            onlyUpdate="true"
            primaryKey="pk_id"
            quotchar="'"
            relativeToChangelogFile="true"
            schemaName="public"
            separator=","
            tableName="person"
            usePreparedStatements="true">
        <column name="address" type="varchar(255)"/>
    </loadUpdateData>
</changeSet>
changeSet:
  id: loadUpdateData-example
  author: liquibase-docs
  objectQuotingStrategy: LEGACY
  changes:
  - loadUpdateData:
      catalogName: cat
      columns:
      - column:
          name: address
          type: varchar(255)
      commentLineStartsWith: A String
      encoding: UTF-8
      file: com/example/users.csv
      onlyUpdate: true
      primaryKey: pk_id
      quotchar: ''''
      relativeToChangelogFile: true
      schemaName: public
      separator: ','
      tableName: person
      usePreparedStatements: true
{
  "changeSet": {
    "id": "loadUpdateData-example",
    "author": "liquibase-docs",
    "objectQuotingStrategy": "LEGACY",
    "changes": [
      {
        "loadUpdateData": {
          "catalogName": "cat",
          "columns": [
            {
              "column": {
                "name": "address",
                "type": "varchar(255)"
              }
            }]
          ,
          "commentLineStartsWith": "A String",
          "encoding": "UTF-8",
          "file": "com/example/users.csv",
          "onlyUpdate": true,
          "primaryKey": "pk_id",
          "quotchar": "'",
          "relativeToChangelogFile": true,
          "schemaName": "public",
          "separator": ",",
          "tableName": "person",
          "usePreparedStatements": true
        }
      }]
    
  }
}

Database Support

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