How to create a temporary table with data from an external table?

Hi,

I want to create a temporary table using data from external database like this one


How do I do that exactly?

Hi, what do you mean by temporary table?

You can create temporary table in your dbms.

However, afaik, you cannot create entity using that temporary.

I usually create another table  that is populated from many tables using job that runs at midnight.

regards

Hi! 

You may create a entity to receive the records and at the end of the work delete all the records used. Only have to remember that you must create a field that identifies each group of records.

Hope this helps

Graça

Hi Yao Teck,

Have a look at this article to learn about integrating OutSystems with an External Database.

You can create a local Entity in your OutSystems Database and synchronize the data from the External Database using a Timer (batch process).

Regards,

Nordin

IBOX wrote:

Hi, what do you mean by temporary table?

You can create temporary table in your dbms.

However, afaik, you cannot create entity using that temporary.

I usually create another table  that is populated from many tables using job that runs at midnight.

regards

If I'm not wrong, you can create (or was it pushing data from external table, not sure) using a timer but I'm not sure how


Nordin Ahdi wrote:

Hi Yao Teck,

Have a look at this article to learn about integrating OutSystems with an External Database.

You can create a local Entity in your OutSystems Database and synchronize the data from the External Database using a Timer (batch process).

Regards,

Nordin


If I want to have 2 of the same tables (for example, UserData and UserData2 tables), do I have to make an additional connection or are there other methods?

Hello Yao,

It is not possible to create "dynamic" tables (or temporary) in OutSystems. Any Entity you want to use, you need to create in development time. 

If you need, for some reason, a temporary table to be created and used, you would have to resort to an extension and do that using C#. As you can imagine, this is not very practical, leading to an increase in time development and maintenance cost.

Maybe you could share with us what is your goal, that you think you need a temporary table so that we could give you more useful insight into how you could achieve your goal without resorting to creating temporary tables?

Cheers.

P.S. As J. have reminded me, using SQL tool during the execution of the query, you can create a temporary table (WITH clause, for example), but by your question, this doesn't seem to be what you need.

Please, let us know what is the requirement so that we can advise you better. 

P.S. 2 Looking a little deeper, I found (with the help from others fellow MVPs, as I am not too used to temporary tables), that you can use temporary tables created in a SQL Tool. But I tested and they do not survive the SQL Tool itself. Didn't test Global Temporary tables, but I doubt they would survive it as well.

And now, just guessing, but...

If you have an external database entity, you can "import" it through an extension, and use it in your OutSystems application as if it was an OutSystems entity.

So, again, why do you think you need a "temporary" table?

Cheers

Eduardo Jauch wrote:

Hello Yao,

It is not possible to create "dynamic" tables (or temporary) in OutSystems. Any Entity you want to use, you need to create in development time. 

If you need, for some reason, a temporary table to be created and used, you would have to resort to an extension and do that using C#. As you can imagine, this is not very practical, leading to an increase in time development and maintenance cost.

Maybe you could share with us what is your goal, that you think you need a temporary table so that we could give you more useful insight into how you could achieve your goal without resorting to creating temporary tables?

Cheers.

My goal is to display two of the same tables from external database side by side in a page, I've tried simply dragging both but it only can allow 1. My boss advised me to use temporary tables, which can clear off the data once the user leave the page but I don't know how.


Solution

Hi Yao Teck Ong,

If all you want is to display on a screen the same table data twice, side-by-side, I see two simple alternatives, neither of which requires you to have any extra (temporary?) tables. Start by placing two Table Records on the screen, each with their Width property set to 6 col:

  • If the data to be displayed is exactly the same on both Table Records (I can't see a scenario where this would be useful but...), then make sure their Source Record List property is configured with the same List (possibly from an Aggregate?)
  • If you want to display data from the same External Entity, but each Table Records requires different filters/sorting, make sure that each Table Records' Source Record List property is configured with a different List (first table is using the output List from the first Aggregate, second table is using the output List from the second Aggregate)

Hope this helps!

Solution

Jorge Martins wrote:

Hi Yao Teck Ong,

If all you want is to display on a screen the same table data twice, side-by-side, I see two simple alternatives, neither of which requires you to have any extra (temporary?) tables. Start by placing two Table Records on the screen, each with their Width property set to 6 col:

  • If the data to be displayed is exactly the same on both Table Records (I can't see a scenario where this would be useful but...), then make sure their Source Record List property is configured with the same List (possibly from an Aggregate?)
  • If you want to display data from the same External Entity, but each Table Records requires different filters/sorting, make sure that each Table Records' Source Record List property is configured with a different List (first table is using the output List from the first Aggregate, second table is using the output List from the second Aggregate)

Hope this helps!

Thanks, this helps!