Column mismatch error while inserting data from one table to another

user_16711351 0
Viewed 42

I'm trying to insert data from one table to another, jud_pers_record_leg_proced_update_202208 is replica of eden_es_master_db.jud_pers_record_leg_proced but when I'm using select distinct in insert query, it consider that eden_es_master_db.jud_pers_record_leg_proced only has those distinct column,

INSERT INTO eden_es_master_db.jud_pers_record_leg_proced_update_202208 partition(edenloaddate) 
select distinct edenloaddate, newidentifierlegalproceeding 
from eden_es_master_db.jud_pers_record_leg_proced 
where personidentifiercao IN (select cao from eden_es_dynamic.fisbajas where cao is not null);

below is the error message

Error: Error while compiling statement: FAILED: SemanticException [Error 10044]: Line 1:12 Cannot insert into target table because column number/types are different 'edenloaddate': Table insclause-0 has 21 columns, but query has 2 columns. (state=42000,code=10044)

bigdata hive
1 Answers

To load into a partitioned table, you need to ensure your last column always be partition column so change your SQL like this -

INSERT INTO eden_es_master_db.jud_pers_record_leg_proced_update_202208 partition(edenloaddate) 
select distinct edenloaddate, newidentifierlegalproceeding , edenloaddate -- last partition column
from eden_es_master_db.jud_pers_record_leg_proced 
where personidentifiercao IN (select cao from eden_es_dynamic.fisbajas where cao is not null);
user_64305230
Related