Hello Everyone,

I have a certain scenario where I would need to INSERT huge quantities of data in the database tables in one go. 

Let me explain with an example: Say I have a master table called as "University". Every University record has multiple "College" records. So - in database terms this is a one to many relationship and the "College" record will have a foreign key for the "University". Now - say that based on the data that I am getting from another database -  I am copying this data to my database in Outsystems.

I need to insert all the "University" and the "College" records -  with their relationships being maintained.

When I am doing this insertion -  I will have to insert a "University" record first followed by multiple inserts in the "College" record. Only then I can move on to inserting another "University"  record. This is because I need to set the FK when inserting into the "College" record (College.UniversityId)

So - I can imagine writing code for this in Outsystems - in a sequential, iterative manner - inserting one record into the entity at a time.

While the sequential, iterative way would work - it is not ideal. It would take huge amount of time (especially if this processing is required between two UI workflows.) It would be even worse - if say the every "College" has say "Specializations" - which also need to be copied piecewise..

Now - I am aware of certain solutions like "BulkInsert" forge component - which can help me do the Bulk Insert for a single entity (multiple records in one go for single entity) - but I cannot use it here. I have a dependency between University and college.

I cannot also use the SQL bulk INSERT ( in SQL we can do bulk insert if we are copying data from one table to another: INSERT INTO <College>  SELECT * FROM <CollegeCopy>.

But I cannot do this either in my case.


Has anyone faced a similar situation in their project?

Have you got a better (faster, non-sequential) way to implement this?

Please let me know.

Hi Chetan,

Why is it not possible to do an INSERT INTO?

Regards,

Daniel

Hello Chetan,

Don't see an "easy" solution here, other than using third party tools to copy your data from one database into another (and you will need access to both databases to do it), or do this one record at a time, using a timer with TimeOut control, based on an excel file.

Cheers.

Daniël Kuhlmann wrote:

Hi Chetan,

Why is it not possible to do an INSERT INTO?

Regards,

Daniel

I think the problem is (probably) that the databases are different (not linked servers), and you are not able to do join betwenn entity of different databases. 

He could export data to an SQL file, adapt it to the new database and run it in the database or using SQL to insert the data, but probably this would require some effort in the preparation of the SQL...


Eduardo Jauch wrote:

Daniël Kuhlmann wrote:

Hi Chetan,

Why is it not possible to do an INSERT INTO?

Regards,

Daniel

I think the problem is (probably) that the databases are different (not linked servers), and you are not able to do join betwenn entity of different databases. 

He could export data to an SQL file, adapt it to the new database and run it in the database or using SQL to insert the data, but probably this would require some effort in the preparation of the SQL...


Hi Eduardo and Daniel, 


Yes that is correct. The databases are not linked servers. I will try the Timer approach.


Just to point out, in the Timer, you don't have to use excel. You can import the tables from the other database with an extension and use them to fetch data to store in the other database.

Cheers.

Eduardo Jauch wrote:

Just to point out, in the Timer, you don't have to use excel. You can import the tables from the other database with an extension and use them to fetch data to store in the other database.

Cheers.

Thank you