Is there any way to loop over a table in a SQL transformation?

Hi Mikel,

If I understood your problem correctly, Table C is a new table that you want to create from a combination of columns from Table A and Table B.

So I don't think you really mean Append when you "declare" table C. (Append means vertically sticking 2 tables together, first each row from one table then each row from the second one, taking the column structure into account.)

I think what you mean, is merge.

And that can be achieved by a LEFT JOIN.

Something like:

CREATE TABLE TABLE_C AS (

   SELECT

         TABLE_A.ID

       , TABLE_A.NAME

       , TABLE_B.*

   FROM

       TABLE_A LEFT JOIN

       TABLE_B ON

       TABLE_A.ID = TABLE_B.ID

   )

TABLE_B.* means every column from TABLE_B but of course you can also just list all the columns you actually need.

 This take ID and NAME columns from TABLE_A and adds all corresponding information from TABLE_B (when available).

Good afternoon,

No, what I am actually looking for is a way to append many selects. I need to combine two tables by appending line 1 in table A with lines 1 to 6 in table B, line 2 in table A with lines 7 to 10 in table B... the way to choose the number of lines in table B depends on the User ID. That is why I was thinking on doing it by going through the lines in table A and append each iteration the line in table A with the lines in table B.

Thank you,

Append would still only work for the same columns in Table A and B.

If this is your table A:

And this is your table B:

The code I suggested would give you the following table C:

If this is not the outcome you are looking for, please provide some example data for better understanding the problem.