[BulkInsert] How to set BulkInsert`s TableName and ColumnsToIgnore?

[BulkInsert] How to set BulkInsert`s TableName and ColumnsToIgnore?

  
Forge Component
(12)
Published on 10 Nov by João Pedro Abreu
12 votes
Published on 10 Nov by João Pedro Abreu

hello,

When using the bulkInsert Server Action, I set these two parameters by following...

TableName = "[OSUSR_69s_Test]"

ColumnsToIgnore =  "Id" 

But these don`t work..

Can you give an example of how to fill these two parameters?

Solution

Hey,

First get the actual Physical table name by using the logical name by using the query:

SELECT PHYSICAL_TABLE_NAME FROM OSSYS_ENTITY WHERE NAME = <NameForOutsystemsEntityInServiceStudio>. Save the output of this query in a text variable. Pass this variable to the BulkInsert server action.

In columns to ignore, you will have to specify the actual column name which is there in database. You can find it dynamically by querying the OSSYS_ENTITY_ATTR table.


Note:

1. Both OSSYS_ENTITY and OSSYS_ENTITY_ATTR are Outsystems system tables and these will always be there.

2. The application tables map to physical tables in database which may have a different name than in the ServiceStudio entitiy.

3. To find the actual name of the table, query OSSYS_ENTITY with where clause specifying the entityName

4. To find actual column name, you can query on JOIN of OSSYS_ENTITY and OSSSY_ENTITY_ATTR and use where clause by logical name of entity and logical name of the column (which are the names given in service studio). 

5. Using the output of the above two queries you will have actual name (physical name) and you can use that in BulkInsert server action parameters.

Note that Physical table name and column name may change on different environments and hence we need to run those queries at runtime.

Let me know if you understood the approach suggested.

Solution

Chetan Yewale wrote:

Hey,

First get the actual Physical table name by using the logical name by using the query:

SELECT PHYSICAL_TABLE_NAME FROM OSSYS_ENTITY WHERE NAME = <NameForOutsystemsEntityInServiceStudio>. Save the output of this query in a text variable. Pass this variable to the BulkInsert server action.

In columns to ignore, you will have to specify the actual column name which is there in database. You can find it dynamically by querying the OSSYS_ENTITY_ATTR table.


Note:

1. Both OSSYS_ENTITY and OSSYS_ENTITY_ATTR are Outsystems system tables and these will always be there.

2. The application tables map to physical tables in database which may have a different name than in the ServiceStudio entitiy.

3. To find the actual name of the table, query OSSYS_ENTITY with where clause specifying the entityName

4. To find actual column name, you can query on JOIN of OSSYS_ENTITY and OSSSY_ENTITY_ATTR and use where clause by logical name of entity and logical name of the column (which are the names given in service studio). 

5. Using the output of the above two queries you will have actual name (physical name) and you can use that in BulkInsert server action parameters.

Note that Physical table name and column name may change on different environments and hence we need to run those queries at runtime.

Let me know if you understood the approach suggested.

Thank you so much for your detailed reply....I will try it and let you know.