Insert into table with same name

[Vivan Jert]

Hello,

I need help with query that will take all tables with prefix "tifa_" in "centraldb" database and copy all data in same name table that ends with "_hist". Column name should be matched. as_of_date need to be yesterday day when running the query.

I have around 70 tables and this is example of 4 tables:

TABLE_NAME ----------- COLUMN_NAME

tifa_empcont ----------- ec_billed_amt_01
tifa_empcont ----------- ec_billed_amt_02
tifa_empcont----------- ec_billed_amt_03
tifa_empcont----------- ec_billed_amt_04
tifa_empcont----------- ec_billed_amt_05

tifa_empcont_hist----------- ec_billed_amt_01
tifa_empcont_hist----------- ec_billed_amt_02
tifa_empcont_hist----------- ec_billed_amt_03
tifa_empcont_hist----------- ec_billed_amt_04
tifa_empcont_hist----------- ec_billed_amt_05
tifa_empcont_hist----------- as_of_date


tifa_employer----------- emp_aca_4980h_trans_flag
tifa_employer----------- emp_aca_98_offer_flag
tifa_employer----------- emp_aca_ale_member_flag
tifa_employer----------- emp_aca_auth_trans_flag
tifa_employer----------- emp_aca_codes_flag

tifa_employer_hist----------- emp_aca_4980h_trans_flag
tifa_employer_hist----------- emp_aca_98_offer_flag
tifa_employer_hist----------- emp_aca_ale_member_flag
tifa_employer_hist----------- emp_aca_auth_trans_flag
tifa_employer_hist----------- emp_aca_codes_flag
tifa_employer_hist----------- as_of_date


Thank you!