Skip navigation
Contact Us
1209 Views 4 Replies Latest reply: Nov 10, 2014 3:01 AM by Nico Heinze RSS
Imamura Yusuke Novice 35 posts since
Sep 4, 2012
Currently Being Moderated

Jan 23, 2013 10:23 PM

Create target table

Hi team,

 

Can PWC 9.1 create Target table dynamically when loading ?

I'd like to change the target table and create the table dynamically in each session.

 

My target db is Oracle 11g.

If PWC can do that, I'd like to use temporary table rather than normal table.

 

Thanks in advance,

Ken


Attachments:
  • Venkat Byrapudi Master 972 posts since
    Mar 25, 2010
    Currently Being Moderated
    Oct 28, 2013 3:26 AM (in response to Imamura Yusuke)
    Re: Create target table

    In database target tables you can't create dynamically.

    Incase of files you can create if you know the max number of columns for target table. based on that you can create & load the for required columns but this is not possible in realtime.

     

    Regards,

    Venkat

  • Kiran Ghatage InfaEmp 606 posts since
    Nov 9, 2012
    Currently Being Moderated
    Nov 9, 2014 11:30 PM (in response to Imamura Yusuke)
    Create target table

    You can go through the following solution from Informatica Marketplace. You will also find over 400 mapping samples and many other useful tools on the same website.

    https://community.informatica.com/solutions/mapping_create_table_using_powercenter

  • Ankit Kansal Apprentice 125 posts since
    Sep 5, 2013
    Currently Being Moderated
    Nov 9, 2014 11:42 PM (in response to Imamura Yusuke)
    Create target table

    Hi Imamura,

                         I haven't tried to create target tables using informatica , but there is one approach which you can try i am not sure whether it will work or not , but trying doesn't harm anything.

     

    1) You can use store_proc to create target table or any data base objects specifically use EXECUTE IMMEDIATE clause with in the procedure for dynamic sql , and insert the data using that same procedure only.

     

    2) In next call check whether the table name exist or not and populate the data using store_procedure only.

     

    If you have an other problem regarding store_proc creation i ll happy to help with.

     

    Please respond whether it worked or not.

     

    Regards,

    -Ankit Kansal

    www.deepinopensource.com

    • Nico Heinze InfaEmp 915 posts since
      May 29, 2002
      Currently Being Moderated
      Nov 10, 2014 3:01 AM (in response to Ankit Kansal)
      Create target table

      You can for example do either of the following:

      • Use a SQL Transformation in a mapping running before your load mapping to create the target mappings on the fly.
      • Use a Pre-Session SQL statement to create the target tables before the actual load session starts.

      At least these are two of the most commonly used approaches.

       

      Regards,

      Nico

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points