Dynamic SQL for Table Creation

[ahendrik]

We have a client that has tables split by month (TranSale202301, TranSale202302, TranSale202303, etc.) They only want to keep the last 25 months of data (so 25 tables). We would like to be able to do this dynamically but the current SQL does not allow it. We tried an MV with jdbc connection but the sql server uses a data agent and from what we know, spark cannot utilize the data agent.

INC_03131301: Failed to load data from [xxxxx] with properties [[database, sqlserver], [connectionPool, 30], [connection, jdbc:sqlserver://xxxx:xxxx;databaseName=xxxx;]] due to [Incorrect syntax near the keyword 'DECLARE'.]

[RADSr]

Do you have access to the SQLServer?   I'd approach it there by creating a view of the most recent 25 months ( using SQL Server functionality to make it dynamic ) and query the one single object w/ Incorta. 

Alternatively suggest loading all data into Incorta non-optimized and then creating an MV filtering on the most recent 25 months for day-to-day analysis.

[ahendrik]

Thank you. That is what we moved forward with but it would be nice to handle this all on our end if possible.