Insert Into Adobe Experience Platfrom (AEP) Query Guidance

[SaurabhCh3]

Hello community,

I am trying to use insert into sql command in AEP query services, my end goal is to create a CTAS schedule queries to ingest data from one dataset to another dataset for a specific phone number column. 

Dataset A has a schema field '_abc.phone.number.number' which has data, Dataset B has a schema field '_abc.uGuestPrimary.phone.number' but is empty. 

I am using below query:

INSERT INTO Dataset B

SELECT struct(_abc as _abc, phone as uGuestPrimary, number as phone, number as number) _abc FROM Dataset A

I am getting following error:
ErrorCode: 58000 Batch query execution gets : [failed reason ErrorCode: 58000 Batch query execution gets : [Analysis error encountered. Reason: [sessionId: 8bed5f80-d51-bb39-090286252d53 queryId: b26abfc3-1a62ab-627fd6504350 Column phone does not exist.]]]

I am using this document for reference:
https://experienceleague.adobe.com/en/docs/experience-platform/query/sql/syntax#select-queries

What could be wrong in my query?

I also tried using last column where I need the data, getting same error.

INSERT INTO Dataset B
SELECT struct(number as number) _abc FROM Dataset A

Thanks all for your time.

[kautuk_sahni]

@abhinavbalooni  @Kumar_Saurabh_  @Ankit_Chaudhary  @Pradeep_Kumar_Srivastav  @dwright  @gkalyan  @Jason_Egan  @narendragandhi  @mittalabhi86  @dhanesh04s Kindly take a moment to review this question and share your valuable insights. Your expertise would be greatly appreciated!

[gkalyan]

@SaurabhCh3 

I guess you already confirmed "phone" column exists in the dataset schema.

If there is a row which does not meet the criteria of the column, all records for the batch will not be inserted into the dataSet. So, I would suggest to look into the dataset with valid rows, otherwise try to run this for a sample data set where you know valid data is available.