I have the following tables
DE1
ID DE_Name Product
1 Sample_1 PRD1
1 Sample_2 PRD2
1 Sample_4 PRD4
2 Sample_3 PRD3
ID DE_Name Product
1 Sample_1 PRD1
1 Sample_2 PRD2
1 Sample_4 PRD4
2 Sample_3 PRD3
Basically column TableName contains actual Table Name that needs to be queried
Sample_1 (DE Name)
Dept_no
123
456
789
Dept_no
123
456
789
Sample_2 (DE Name)
Dept_no
125
457
Dept_no
125
457
Sample_3 (DE Name)
Dept_no
025
0157
0010
12456
Dept_no
025
0157
0010
12456
Sample_4 (DE Name)
Dept_no
12345
457
79000
98345
Dept_no
12345
457
79000
98345
Desired Output (Into a New DE)
Table 2
ID Dept_no Product
1 123 PRD1
1 456 PRD1
1 789 PRD1
1 125 PRD2
1 457 PRD2
1 12345 PRD4
1 457 PRD4
1 79000 PRD4
1 98345 PRD4
2 025 PRD3
2 0157 PRD3
2 0010 PRD3
2 12456 PRD3
ID Dept_no Product
1 123 PRD1
1 456 PRD1
1 789 PRD1
1 125 PRD2
1 457 PRD2
1 12345 PRD4
1 457 PRD4
1 79000 PRD4
1 98345 PRD4
2 025 PRD3
2 0157 PRD3
2 0010 PRD3
2 12456 PRD3
Can you please let me know if we can achieve desired output DE set through SQL or do we need SSJS.
I tried the scripting in JS and it worked fine except that salesforce has limiation to extract only 2500 rows per retrive from a DE
I am thinking of using cursors but SFMC SQL doesnt seem to understand cursors.
can anyone please provide any insights on how to get data into Table2 format?
