Auto increment year value dynamically to multiple tables

[pavannarani]

Hello people,

I am not sure this is the right approach or right question to ask. But i have a requirement to update (increment year value with 1 ex - 2023 to 2024) only date columns in all fact tables. So instead of updating every table i decided to use stored procedure and store all update scripts for every table and i tested it its working fine as expected.

But i would like to know is there any way i can update the date columns in multple tables because schema will be different for each table anyway. with the below script i was able to pull the particular table date columns with system defined tables.

As per above snapshot am able to pull Date columns in warehouse, so i thought like if i can pass table names one by one as parameter to stored procedure through data pipeline for every table it returns only those date columns similarly right. And here my idea is to use these columns in loop and i wanted to keep only one UPDATE statement inside that loop. By keeping it i wanted to pass those date column names itself one by one in loop (i dont know its possible or not but i felt it would be easier so i can make it dynamic for all tables at a time)

EX - UPDATE TableName SET ColumnParameter = DATEADD(YEAR, 1, ColumnParameter)

and anyway i will be triggering stored procedure through data pipeline ryt so for every execution only one table will be executed in stored procedure.

I want to know is it possible in fabric warehouse i tried using CURSORS its not working, even while loop i tried it seems doesnt work. Is there any better idea how can i make this updation for all tables dynamic i didnt find any article for this. Please suggest is my idea is correct or any other approach is there to achieve this requirement.

Thanks in advance.

Regards

Pavan Kumar

[lbendlin]

Why do you need to add these years in the first place?  Can't you leave your data as is and carry a reference table with the year offset for each table?