title: spark sql Insert string column to struct of array type column
body: <p>I am trying to insert a STRING type column to an ARRAY of STRUCT TYPE column, but facing errors. Could you help to provide the right direction to do the INSERT.</p>
  <p>In databricks notebook, I have a raw table (<code>raw_lms.rawTable</code>) where all the columns are string type. This needs to insert into a transform table (<code>tl_lms.transformedTable</code>) where the columns are array of struct type.</p>
  <pre><code>CREATE TABLE raw_lms.rawTable
   (  PrimaryOwners STRING
    ,Owners STRING
   )
   USING DELTA LOCATION 'xxxx/rawTable'
  </code></pre>
  <pre><code>CREATE TABLE tl_lms.transformedTable
   (  PrimaryOwners array&lt;struct&lt;Id:STRING&gt;&gt;
    ,Owners array&lt;struct&lt;Id:STRING&gt;&gt;
   )
   USING DELTA LOCATION 'xxxx/transformedTable'
  </code></pre>
  <p>Raw table has the below values populated: Eg.</p>
  <pre><code>INSERT INTO TABLE raw_lms.rawTable
  VALUES
  (&quot;[{'Id': '1393fe1b-bba2-4343-dff0-08d9dea59a03'}, {'Id': 'cf2e6549-5d07-458c-9d30-08d9dd5885cf'}]&quot;,
   &quot;[]&quot;
  )
  </code></pre>
  <p><a href="https://i.stack.imgur.com/1zA0u.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1zA0u.png" alt="enter image description here" /></a></p>
  <p>I try to insert to transform table and get the below error:</p>
  <pre><code>INSERT INTO tl_lms.transformedTable 
  SELECT PrimaryOwners,
         Owners
  FROM raw_lms.rawTable
  </code></pre>
  <blockquote>
  <p>Error in SQL statement: AnalysisException: cannot resolve
  'spark_catalog.raw_lms.rawTable.<code>PrimaryOwners</code>' due to data type
  mismatch: cannot cast string to array&lt;struct&lt;Id:string&gt;&gt;;</p>
  </blockquote>
  <p>I do not want to explode the data. I only need to simply insert row for a row between <code>rawTable</code> and <code>transformedTable</code> of different column data types.</p>
  <p>Thanks for your time and help.</p>