Finance | Project Operations, Human Resources, ...

Trying to export TABLEIDTABLE to the data lake

(0) Share

Report

Posted on by AstridMalanka

Hello,

I need the table TABLEIDTABLE exported to the lake. But I am not even able to see the table on the AOT.

I have a view that uses that underline table and I understand that the values could vary on the environments, so it would be to hard code it.

Is there a way to do it?

I have the same question (0)

All responses (13)

Answers (0)

Suggested answer

André Arnaud de Cal... 301,075 Super User 2025 Season 2 on at

Like (0)

Report

Hi Astrid,

I wonder why you would need table ID numbers in the data lake. What is the (reporting) requirement? Anyway, you can create a data entity based on the table SQLDictionary. You would only need to have the records where the FieldID is zero for the table number and table names.

Was this reply helpful? Yes No
AstridMalanka 8 on at

Like (0)

Report
Good morning,

There are some views that call for the table TABLEIDTABLE. For example: VENDORINVOICECHARGESVIEW, and I need the view for a entity schema.

In order to export an entity schema on the lake, you need to be able to export the table. Since the new version was released we are able to export SQLDictionary, but that table will not give me the table name, just the ID, and the view (in this case, calls for the table name).

SELECT COUNT(RECID) FROM VENDINVOICEINFOSUBTABLE T4 WHERE T4.TABLEREFID = T1.TABLEREFID AND T4.DATAAREAID = T1.DATAAREAID) = 1 THEN (SELECT ID FROM TABLEIDTABLE T2 WHERE T2.NAME = 'VENDINVOICEINFOSUBTABLE') ELSE (SELECT ID FROM TABLEIDTABLE T3 WHERE T3.NAME = 'VENDINVOICEINFOTABLE') END) AS TRANSRECID) AS NVARCHAR(10))) AS TRANSTABLEID FROM VENDINVOICEINFOTABLE T1

I have hardcoded the table ID, but if in production is different, it will be an issue.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,075 Super User 2025 Season 2 on at

Like (0)

Report

Hi Astrid,

I still don't understand why you would need a table ID in the data lake. When exporting data, usually the natural key will be exported.

The coding you shared is T-SQL to calculate a virtual column. That will be executed on SQL where the TableIdTable is present and known.

Have you tried adding the VendorInvoiceChargesView as data source for your data entity? On a data entity, you can also add a computed column like it is done on this view.

PS. The DataDictionary table has a column for the name which contains the table name in case the field ID is 0.

Was this reply helpful? Yes No
AstridMalanka 8 on at

Like (0)

Report

When I try to export it says the following tables/views are missing. It is not an normal export, it is exporting through the lake, it does not work as a job export. Maybe I am not explaining myself?

Was this reply helpful? Yes No
André Arnaud de Cal... 301,075 Super User 2025 Season 2 on at

Like (0)

Report

Hi Astrid,

Is your last reply related to the original question? On the screenshot you shared, I don't see any warning or error message.

Or do you want to clarify you are exporting the tables directly and you are not using data entities?

Was this reply helpful? Yes No
AstridMalanka 8 on at

Like (0)

Report

Good morning, I was trying to explain what I am doing.

I have a connector to my UAT environment that lets me export tables and entities schemas to the lake.

From there I use the CDMUtilSolution to read the json and csv files into views and tables on synapse.

One of the entities, has a view that is calling that table.

Was this reply helpful? Yes No
Suggested answer

SAADULLAH M Z 175 on at

Like (0)

Report

You can use SYSTABLEIDVIEW

This is available in AOT > System Documentation > Views

Hope this will help you.

Was this reply helpful? Yes No
AstridMalanka 8 on at

Like (0)

Report

Hi, the SYSTABLEIDVIEW, pull data for a table of a similar name, but when I checked the database the other day, the table was empty, the table is also not exposed on the lake.

Was this reply helpful? Yes No
SAADULLAH M Z 175 on at

Like (0)

Report

Create a new View add the SYSTABLEIDVIEW as data source and create a new entity and expose this entity where ever you want.

Was this reply helpful? Yes No
AstridMalanka 8 on at

Like (0)

Report

when exporting to the lake, you need the underline table, because you export tables, if they are not exposed i wont be able to export it.

Was this reply helpful? Yes No