Table of Contents

Search

  1. Preface
  2. Introduction to Informatica Data Engineering Integration
  3. Mappings
  4. Mapping Optimization
  5. Sources
  6. Targets
  7. Transformations
  8. Python Transformation
  9. Data Preview
  10. Cluster Workflows
  11. Profiles
  12. Monitoring
  13. Hierarchical Data Processing
  14. Hierarchical Data Processing Configuration
  15. Hierarchical Data Processing with Schema Changes
  16. Intelligent Structure Models
  17. Blockchain
  18. Stateful Computing
  19. Appendix A: Connections Reference
  20. Appendix B: Data Type Reference
  21. Appendix C: Function Reference

Using the CREATE Strategy with Post-SQL

Using the CREATE Strategy with Post-SQL

Use the CREATE strategy and a post-SQL statement to add a subset of the source schema's new columns to the existing target table.
  1. Create a passthrough mapping with ports dynamically linked from the Read transformation to the Write transformation.
  2. Select the Write transformation and the Advanced property sheet. For Target Schema Strategy, select
    Create.
  3. In the Update Strategy property, select
    Update as Update.
  4. In the PostSQL property, use an ALTER TABLE command to specify the columns to add. See the example below.
    You can choose to put the Post-SQL query itself into the PostSQL property, or use a parameter or parameter set that contains the query. For more information about using parameterization in mappings, see the
    Informatica Developer Tool Guide
    and the
    Informatica Developer Mapping Guide
    .
    The following image shows the PostSQL statement in the Advanced properties:
    The image shows the Advanced properties sheet with the PostSQL property populated with a Post-SQL query.
Example
Use the SQL statement below as a template for the post-SQL statement.
ALTER TABLE default.deltatgt add column addition4 int{INFA_SQL_SPLITTER}MERGE INTO default.deltatgt USING default.tmp_table_auto3 ON default.deltatgt.idt = default.tmp_table_auto3.idt WHEN MATCHED THEN UPDATE SET default.deltatgt.addition4 = default.tmp_table_auto3.addition4 WHEN NOT MATCHED THEN INSERT (dept, name, idt, addition, addition2, addition3, addition4) VALUES (dept, name, idt, addition, addition2, addition3, addition4)