To homepage To homepage
Solved

Populate Existing table

  • 28 April 2023
  • 8 replies
  • 109 views
L
Rank

Hi,

I would like to “misuse” TX as an ETL tool by populating an Existing table with new rows.

All the logic necessary is processed using TX. The end table to be filled is an existing table and it's table structure can't be modified  so I also need to omit the system control fields. I planned to use an incremental load table but getting errors that the table already exist when I deploy.

Not sure if we can use TX this way and someone already solved such a problem

Any help would be appreciated.

icon

Best answer by Lorenzo 9 May 2023, 12:30

View original

8 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Lorenzo

There are some tools we have applied that can be used to add rows from other tables.

You can add more rows to an existing table by using the Add Related Records, which using some rule will add rows from another table to an already existing table.

You can also use the Table Insert to do this and this does not require any relations for figuring out what to add.

If this will not work for you there is a third option, which is to use a script action to add missing data as a post data transfer or post data cleansing step.

How is the existing table set up and where does the other data come from?

L
Rank

@Thomas Lind  

Forgot to mention that I already tried the Table Insert. But the trouble there is that when you try to deploy or try to preview the data I get an error due to missing system control fields. When you deploy then the table is re-created with only the system control fields. The custom table insert is not taken into account.

Regarding your question, The table is created outside TX, is not a TimeXtender table and the data to be inserted is created in TX.

Is there a way to switch of the re-create?

I will try the other 2 options you mentioned.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Lorenzo

Is there any way you can show how this is not possible, the table insert, with some screendumps of the setup you attempted to use?

It should be able to handle our own system fields.

 

 

L
Rank

 Please don't look at the names, I have used when I tried it the same name LFW_Subscriptions but because I want to try the other options I have saved it as CI.

The system fields are automatically created but I don't want them for the existing table.  

 

 

rory.smith
Rank
Userlevel 5
Badge +7

Hi,

given that the target table is not managed by TX I would not make a table for it in your project. Otherwise you will always get system fields and a deploy of the table.

Just make a Stored Procedure that inserts the data prepared in TX to the target table. If the target table is in a different database than where the prepared data in TX is you may need to create a dummy table (managed by TX) to link the triggering of the stored procedure to.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Lorenzo

I agree with Rory, it does not seem necessary to make this a table in TX.

L
Rank

Thanks Rory, especially for the hint to create a dummy table.

To use the stored procedure works and solved my problem.

For those who need default info, what I did:

  • Created and populated a table to store all the data I would need to insert rows in my table that is not a TX table
  • Created a stored procedure (Insert into Table (fields ….) SELECT FROM TX table
  • Created a Dummy table, No fields (automatically system fields are added)
  • Added a script action to execute the procedure
  • Added a Post step (to trigger the script action) to the table (Advanced → Set pre- and post script)
Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Lorenzo

Good you go it to work, I will set your answer as the best one.

Regards

Thomas Lind

Reply


Terms and ConditionsCookie settings