title: Snowflake how can we loop over each row of a temp table and insert its values with into another table where each field with its value is a single row?
body: <p>We are loading data into a fact table, we the original temporary table on Snowflake looks like the following:</p>
  <p><a href="https://i.stack.imgur.com/A9cIj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/A9cIj.png" alt="enter image description here" /></a></p>
  <p>Where <code>indicator_nbr</code> fields are questions asked within a survey.</p>
  <p>We are using data modelling techniques in building our warehouse database, so the data will be added into a fact table like so:</p>
  <p><a href="https://i.stack.imgur.com/PUsWH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PUsWH.png" alt="enter image description here" /></a></p>
  <p>Then the same for the indicator 2 and 3 and so on if there is other questions.</p>
  <p>Each Field with its value will be as a single row. Of course there is other metadata to be added like <code>load_dt</code> and <code>record_src</code> but they are not a problem.</p>
  <p>The current script is doing the following:</p>
  <ol>
  <li><p>Get the fields into an array =&gt; <code>fields_array = ['indicator_1', 'indicator_2', 'indicator_3']</code></p>
  </li>
  <li><p>A loop will run over the array and start adding each field with its value for each row. So imagine we are having 100 rows, we will run 300 inserts, one at a time:</p>
  <p><code>for (var col_num = 0; col_num&lt;fields_array.length; col_num = col_num+1) {</code></p>
  <pre><code>    var COL_NAME = fields_array[col_num];
  
      var field_value_query = &quot;INSERT INTO SAT_FIELD_VALUE SELECT md5(id), CURRENT_TIMESTAMP(), NULL, 'SRC', &quot;+COL_NAME+&quot;, md5(foreign_key_field) FROM &quot;+TEMP_TABLE_NAME+&quot;&quot;;
  </code></pre>
  <p>}</p>
  </li>
  </ol>
  <p>As mentioned in the comment on <a href="https://stackoverflow.com/questions/66346079/snowflake-how-to-optimize-a-javascript-procedure-that-loops-over-an-array-and-in?noredirect=1#comment117353910_66346079">this post</a> showing the full script, it is better to loop over a string concatenating each <code>from values</code> of the insert query.</p>
  <p>There is 2 issues of the suggested solution:</p>
  <ol>
  <li>There is a size limit of a query on Snowflake (it should be less than 1 MB);</li>
  <li>if we are going to loop over each field and concatenate the <code>from values</code>, we should do a <code>select</code> query as well from the temp table to get the value of the column, so there will be no optimization, or we will reduce the time a little bit but not to much.</li>
  </ol>
  <p><strong>EDIT: A possible solution</strong></p>
  <p>I was thinking of doing an sql query selecting everything from the temp table, and do hashing and everything and save it into an array after transposing, but I have no idea how to do it.</p>