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
| Name | Description |
|---|---|
| catalogName | Name of the catalog@ v3.0E.g. 'cat' |
| commentLineStartsWith | Lines starting with this character are treated as comment and ignored.Default: '#' |
| encoding | Encoding of the CSV file (defaults to UTF-8)Default: 'utf-8' |
| file | CSV file to loadE.g. 'com/example/users.csv' |
| onlyUpdate | booleanIf true, records with no matching database record should be ignored@ v3.3 |
| primaryKey | Comma delimited list of the columns for the primary keyE.g. 'pk_id' |
| quotchar | The quote character for string fields containing the separator character.Default: '"' |
| relativeToChangelogFile | booleanWhether the file path is relative to the root changelog file rather than to the classpath. |
| schemaName | Name of the schemaE.g. 'public' |
| separator | Character separating the fields.Default: ',' |
| tableName | Name of the table to insert or update data inE.g. 'person' |
| usePreparedStatements | booleanUse prepared statements instead of insert statement strings if the DB supports it |
Nested Properties
| Name | Description | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| columns / column [0..N] | CSV -> table column mapping can be defined.
Either the 'header' or 'index' attribute needs to be defined for columns if the header name in the CSV is different than the column needs to be inserted
If no `column` defined at all, header names has to match the column names in the table.The column type it is taken from the DB. Otherwise for non-string columns the type definition might be requiredNote: columns tag not required in XML Attributes
|
<changeSet author="liquibase-docs" id="loadUpdateData-example">
<loadUpdateData catalogName="cat"
commentLineStartsWith="/"
encoding="UTF-8"
file="com/example/users.csv"
onlyUpdate="true"
primaryKey="pk_id"
quotchar="'"
relativeToChangelogFile="true"
schemaName="public"
separator=";"
tableName="person"
usePreparedStatements="true">
<column header="header1"
name="id"
type="NUMERIC"/>
<column index="3"
name="name"
type="BOOLEAN"/>
</loadUpdateData>
</changeSet>changeSet:
id: loadUpdateData-example
author: liquibase-docs
changes:
- loadUpdateData:
catalogName: cat
columns:
- column:
header: header1
name: id
type: NUMERIC
- column:
index: 3
name: name
type: BOOLEAN
commentLineStartsWith: /
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",
"changes": [
{
"loadUpdateData": {
"catalogName": "cat",
"columns": [
{
"column": {
"header": "header1",
"name": "id",
"type": "NUMERIC"
}
},
{
"column": {
"index": 3,
"name": "name",
"type": "BOOLEAN"
}
}]
,
"commentLineStartsWith": "/",
"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
| Database | Notes | Auto Rollback |
|---|---|---|
| DB2/LUW | Supported | No |
| DB2/z | Supported | No |
| Derby | Supported | No |
| Firebird | Supported | No |
| H2 | Supported | No |
| HyperSQL | Supported | No |
| INGRES | Supported | No |
| Informix | Supported | No |
| MariaDB | Supported | No |
| MySQL | Supported | No |
| Oracle | Supported | No |
| PostgreSQL | Supported | No |
| SQL Server | Supported | No |
| SQLite | Supported | No |
| Sybase | Supported | No |
| Sybase Anywhere | Supported | No |