How to insert data with existing id values?

Rank: #14200

How to insert data with existing id values?

Question

I'm trying to bootstrap data by excel to Outsystems database. The data include a Id field (Auto Number).

If set "Is AutoNumber" to Yes to Id attribute, then bootstrap data will ignore the Id values in excel. I also tried to set "Is AutoNumber" to No, then bootstrap data success. After that I try change "Is AutoNumber" to Yes , but it occur error when publishing.

Is there any solution for migrate data with Id values to Outsystems database?

on 5th Jan

Shweta Gedam

Rank: #286

Hi Cindy,

You don't have to include ID attribute in your excel file as outsystems automatically insert numbers for Id attribute in an increasing order during bootstrap :)

Please try to remove Id column from your excel and try to import data in to the database then.

For more information, you can take a look at the below documentation.

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Bootstrap_an_Entity_Using_an_Excel_File

Hope it helps, Thanks :)

on 5th Jan

Afonso Carvalho

mvp_badge

MVP

Rank: #44

Hi Cindy,

Are your Ids in the Excel data sequential? You could order your data by Id, then delete the column and let the autonumber generate the expected Ids.

on 5th Jan

5 replies

Last reply on 5th Jan

Show thread

Hide thread

Cindy Ho

Rank: #14200

Ids not sequential and maybe missing some number.

on 5th Jan

Sachin Waghmare

Rank: #386

Replying to Cindy Ho's comment on 05 Jan 2021 09:55:06

Do you have any business impact of that column which is having missing numbers?

on 5th Jan

Cindy Ho

Rank: #14200

Replying to Sachin Waghmare's comment on 05 Jan 2021 10:03:42

Id is a foreign key of other entity. If Id changed, it will affect the data relation.

on 5th Jan

Shweta Gedam

Rank: #286

Replying to Cindy Ho's comment on 05 Jan 2021 10:15:54

Hi Cindy,

If I understood your problem correctly, you need to make some modifications in your Bootstrap Entity server action.

Refer to the following link and In For Each loop, assign coming Id in to your new entity source assignment then use CreateEntity Action to create records with the value stored in Id column of excel.

https://success.outsystems.com/Documentation/How-to_Guides/Data/How_to_update_a_Bootstrap_Action_to_fetch_an_entity_identifier_from_Excel

Hope this helps!!

on 5th Jan

Sachin Waghmare

Rank: #386

Replying to Cindy Ho's comment on 05 Jan 2021 10:15:54

If this ID is used as foreign key to other entity attribute then set AutoNumber to No and it's better to write logic to generate a new unique value to store into ID attribute, whenever you insert a new record into this bootstrapped entity.

on 5th Jan

Sachin Waghmare

Rank: #386

Hi Cindy,

Yes, you can do bootstrap with ID column with Integer data type. However, it would be better to keep default ID attribute which will be set Auto number as "Yes" and Datatype as "Long Integer" for any data action. And, once your data get uploaded to OS database, you can remove your ID column (Excel one) from entity and use OS default ID column for any CRUD operations.

Thanks & Kind Regards,

Sachin

on 5th Jan

1 reply

on 5th Jan

Show thread

Hide thread

Cindy Ho

Rank: #14200

I want keep the old Id value as identifier.

on 5th Jan

Plácido Miranda

Rank: #1032

Hi Cindy,

you cannot define a table with some fix values for the id, insert data, and after that change to be auto number.

What you can do is to create two columns id's. The internal (id) as auto number for the plataform and a externalId.

Plácido

on 5th Jan

Stephen Li

Rank: #7579

Hi all, I am Cindy's colleague and please let me continue the conversation here.

We have thought of creating a new field in the tables in order to contain the OldId while maintaining a NewId for OS. If doing it in this way, we either rely on the OldId forever for relationship mapping or we remap all the relationships into the NewId then abandon the OldId field afterwards. However, there are some CONS that I could think of:

1. there are over 100 db tables in the project and remapping all of them will take up a lot of man power and time to do the logic.

2. this method may serve this project right, but if the project has other external application with db tables that reference us, then it require all these external applications to remap their relationships and that's an enormous work.

The best way I could think of is to insert the exact value of OldId into the NewId field of Outsystems directly so we don't have to remap the relationship at all but can use such values for PK. Therefore, we want to know if there is a good way to achieve it?

on 6th Jan

1 reply

on 6th Jan

Show thread

Hide thread

Kilian Hekhuis

mvp_badge

MVP

Rank: #2

Hi Stephen,

If you have a good grasp of the database tables, and know the exact relations between them, you could do the mapping while bootstrapping the data. You'll need a temporary Entity that has three Attributes: TableName, OriginalId and NewId, with a single composite index on TableName + OriginalId. You can do then the following:

For each table, read the data from the Excel file, Create the record in the OutSystems database, and store the table name, Id from the Excel file and Id from the Create in the table;
If the table contains one or more foreign keys to previously bootstrapped tables, use the table name of the foreign key and the foreign key Id in the Excel file to find the new Id in the OutSystems database. Update the Entity Record with these new Ids before Create-ing them.

This is also quite some work with a 100+ tables that have a lot of interdependencies, but at least you don't need to create extra columns and whatnot.

on 6th Jan