Insert into delta table fails

[elgeo]

Hello experts. We are trying to execute an insert command with less columns than the target table:

Insert into table_name( col1, col2, col10)

Select col1, col2, col10

from table_name2

However the above fails with:

Error in SQL statement: DeltaAnalysisException: Column col3 is not specified in INSERT

Do you know if INSERT using a subquery requires all the columns of the target table specified?

[UmaMahesh1 · Accepted solution]

Hi @ELENI GEORGOUSI​ 

Yes. When you are doing an insert, your provided schema should match with the target schema else it would throw an error.

But you can still insert the data using another approach. Create a dataframe with your data having less columns and when writing that data into the target path, enable the mergeSchema option. This way even if you are adding new columns or removing columns, data would not be lost.

Hope this helps.

Cheers.