ApatarForge
  Dashboard > INTEGRATION GUIDES > Integration Guides > Table Update
  INTEGRATION GUIDES Log In   View a printable version of the current page.  
  Table Update
Added by Alex Khizhnyak, last edited by Alex Khizhnyak on Feb 15, 2010  (view change)
Labels: 
(None)

This guide will help you learn how to update records in a table, e.g. overwrite only the entries that have been changed.

Modes available

Transformations can be run in one of the four modes:

Insert (available in all connectors)
Update (available in all connectors)
Synchronization (available in the connector for Salesforce CRM)
Delete (available in all JDBC connectors: AS400, DB2, DBase, EnterpriseDB, Firebird, MS Access, MS SQL, MySQL, ODBC, OpenBravo, OpenEdge, Oracle, PostgreSQL, SyBASE, and Vertica)

Configuration

To update a table, you need to open the output database connector and switch it to the Update mode during configuring the connector's properties. You can do this when the list of the source data fields is being displayed.

If you select Update, Synchronization, or Delete mode, you will also need to select a field or set of fields to be used as primary keys. Values of these fields will be used to compare incoming records to the ones that already exist in your data target and define if a record should be updated, added, or deleted.

To make sure the right records are updated/deleted, select the field(s) that has unique values as the primary key. It can be, for example, a field containing some sort of ID that's also contained in your data source, or a combination of fields that together will form a value that's different for each record.

Make sure to have input fields mapped to the field(s) selected as primary key in an operation linked to your target connector.

Difference between modes

Insert mode adds records to your data target regardless of whether such records exist in the target or not.

Update more looks for matching records based on the primary key set, updates existing records that match incoming records, and creates new records if no match was found.

Synchronization looks for matching records in the target. Then if there's a record in data target that matches an incoming record, it's updated; if an incoming record doesn't match any record that's already contained in your data target, a new record is created; if there's a record in your data target that doesn't match any incoming record, such record is deleted from your data target.

To synchronize data using connectors that don't have synchronization mode enabled, you can select Insert mode and tick 'Clear the selected table before any data written' at the last screen of your target connector. What will happen in this case is that all records that are already there in your target table will be deleted and records from your data source will be inserted. This way you will have 100% match between data in your source and target

Delete mode looks for matching records based on the primary key set and deletes them from your data target.

In the examples below "Company" is the field set as primary key:

Source table:

No. Company FirstName LastName Email Phone Title
1 ABC John Smith J@abc.com 123 CEO
2 Microsoft Jim Black jim@ms.com   QA Engineer
3 Joomla Phil Collins PC@joo.cn    

Target table:

No. Company FirstName LastName Email Phone Title
1 ABC John Smith J@abc.com 123 123 5766  
2 IBM Kate Adams   235 888 9966  
3 Microsoft Jim Black jim@ms.com   QA Engineer

Transformation in the Insert mode:

No. Company FirstName LastName Email Phone Title
1 ABC John Smith J@abc.com 123 123 5766  
2 IBM Kate Adams   235 888 9966  
3 Microsoft Jim Black jim@ms.com   QA Engineer
4 ABC John Smith J@abc.com 123 CEO
5 Microsoft Jim Black jim@ms.com   QA Engineer
6 Joomla Phil Collins PC@joo.cn    

(Records from the source table have been added to the target table regardless of where they existed there before or not.)

Transformation in the Update mode:

No. Company FirstName LastName Email Phone Title
1 ABC John Smith J@abc.com 123 CEO
2 IBM Kate Adams   235 888 9966  
3 Microsoft Jim Black jim@ms.com   QA Engineer
4 Joomla Phil Collins PC@joo.cn    

(ABC and Microsoft records have been updated, as it exists in both the source and target tables; Joomla record has been added, as it didn't exist in the target table.)

Transformation in the Synchronization mode:

No. Company FirstName LastName Email Phone Title
1 ABC John Smith J@abc.com 123 CEO
2 Microsoft Jim Black jim@ms.com   QA Engineer
3 Joomla Phil Collins PC@joo.cn    

(ABC and Microsoft records have been updated; IBM record has been deleted; and Joomla contact has been added.)

Transformation in the Delete mode:

No. Company FirstName LastName Email Phone Title
2 IBM Kate Adams   235 888 9966  

(ABC and Microsoft records have been deleted; IBM record has been preserved as there was no matching record in the source table; Joomla record hasn't been created)

excuse me

cau u explain again to me

how to update data ??

i have try like you but its fail and make the data empty.

also i want to ask, how to delete data with apatar ?

and apatar use query like this

"select field from table where name like '%a5%'

 thanks,

randy 

Hello, Randy!

For this purpose, you need to use the Synchronization mode.
If any record is deleted in the source table, it will be deleted in the target table.

Or, you can also delete ALL entries, if you choose "Clear the selected table..." in the Update/Insert mode.

Site running on a free Atlassian Confluence Open Source Project License granted to Apatar Data Integration (ETL). Evaluate Confluence today.
Contact Administrators