pfx-sql:insert

Summary: Insert data into an external SQL database table.

URI Format

pfx-sql:insert?table=myTable&dataSource=myDb

Parameters

Parameter	Type	Default	Required	Description
`dataSource`	String	`dataSource`	No	Name of the Spring DataSource bean
`table`	String		Yes	Target database table
`mapper`	String		No	Mapper ID for field transformation
`businessKeys`	String		No	Comma-separated business key columns (used for deduplication before insert)
`dialect`	SQLDialect	`MYSQL`	No	SQL dialect
`batchSize`	Integer	`5000`	No	Batch size for database writes

Examples

Basic Insert

XML

<route id="insertToDb">
    <from uri="timer://export?repeatCount=1"/>
    <to uri="pfx-api:fetch?objectType=P&amp;filter=allProducts"/>
    <to uri="pfx-sql:insert?table=products&amp;dataSource=myDb"/>
</route>

Insert with Mapper

XML

<route id="insertWithMapper">
    <from uri="timer://export?repeatCount=1"/>
    <to uri="pfx-api:fetch?objectType=P&amp;filter=allProducts"/>
    <to uri="pfx-sql:insert?table=products&amp;mapper=dbMapper&amp;dataSource=myDb"/>
</route>

Insert with Business Keys (Deduplication)

XML

<route id="insertDedup">
    <from uri="timer://export?repeatCount=1"/>
    <to uri="pfx-api:fetch?objectType=P"/>
    <to uri="pfx-sql:insert?table=products&amp;businessKeys=sku&amp;dataSource=myDb"/>
</route>

Common Pitfalls

Problem	Cause	Fix
`table` not specified	Missing required parameter	Add `table=yourTable` to the URI
Duplicate key errors	Table has unique constraints and data has duplicates	Use `upsert` instead, or add `businessKeys` for pre-insert deduplication
Column mismatch	Body fields don't match table columns	Use a `mapper` to transform fields before insert
Wrong dialect	Default is MYSQL but target is different	Set `dialect=POSTGRESQL` (or ORACLE, MSSQL) explicitly

See Also

pfx-sql Component — Parent reference
pfx-sql:upsert — Insert or update by business key