I want to transfer tables from an existing MySQL Database to Outsystems.

I tried to export to Excel and Bootstrap the Entities from Excel, but the existing tables already have an Id Column, and I do not want to use the Id column created by Outsystems by default.

Is there a way to overcome this or any other way to import the tables without any issues?


Regards,


Marios

Hello Mario,

In OutSystems, when creating the entities (and before publishing the first time), change the property AutoNumber o the ID attribute to False.

Now you have to provide an ID for each field you create.

I would avoid this, thou... As you will become dependent on creating your own IDs.

What's the problem with the OutSystems IDs?
You can have lookup tables (just for the import of data) and fix any FK in a table to point to the new ID, for example.

Cheers.

Hi Marios Andrea,

Just a suggestion try to upload the excel which you have , then change that identifier attribute to the attribute what you need as a identifier , then delete the outsystems Default Id.


Manikandan K wrote:

Hi Marios Andrea,

Just a suggestion try to upload the excel which you have , then change that identifier attribute to the attribute what you need as a identifier , then delete the outsystems Default Id.


As Far as I know, this will not work, Manikandan.
When you delete an attribute, the field is not deleted from the physical table.

As far as I know, the old ID will keep it's constraint... 

At least in the past, I wasn't able to delete the ID of an entity... Didn't tested this on newer versions thou...

Did you tested it?


Manikandan K wrote:

This solution works, but I can't set the existing ID column from Integer to Autonumber.

This is the error I get when I try to publish it:

Database Upgrade Error
Column 'OSUSR_QHA_MVSTUDIO.ID' exists in database as Integer (int) but the new version is defined as Integer (Autonumber). Database int columns can not be changed to Autonumber.


The reason I want to have the Id column created by the platform deleted is that I already have an ID column in the existing database, which is the identifier and auto-increment as well.


Is there any other way to import tables from existing databases into Outsystems?


Thanks!


Marios

Hello Marios,

My experience is: Once you publish an entity, you can't change the Primary Key anymore, in any way.

Not even deleting it.

At least through normal ways using Service Studio or Service Center. There is a paid tool to migrating data. You can find it on Forge. it's from InfoSystema if my memory is ok. Never used it.

Other than that, I tend to create an import app to import creating new IDs, with help of lookup tables to make the connection between old and new ids to fix FKs.

Cheers

Thanks Eduardo,


I think I will create the tables manually and then Create An Action to Bootstrap Data from Excel.

I just wanted to find a way to avoid all that :)


Regards,


Marios

We are running into this issue now, needing to import existing IDs from a legacy system, and OutSystems has told us it is NOT possible to import existing ID values into the ID of records in OutSystems.

However, it could be possible if OutSystems would allow us to set IDENTITY_INSERT on:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017

Depending on your environment, you may or may not have the permissions to do this before you import your data. See this thread for more info:

https://www.outsystems.com/forums/discussion/18594/etl-identity-insert/